=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

I'd like to have this same number returned, but I'd like it to filter out
when

=Data!M1:M2500 does NOT equal "FA" or "Nonrecordable".

Can anyone help me with this?

Conversely, I'd like the same calculation in another cell

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

that adds exclusively counts when the values of
=Data!M1:M2500 values are "FA" or Recordable"

Thank you.

try

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(Data !M$1:M$2514<{"FA", "Nonrecordable"}))

change < to = for the second part of your question.

"Gina" wrote:

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

I'd like to have this same number returned, but I'd like it to filter out
when

=Data!M1:M2500 does NOT equal "FA" or "Nonrecordable".

Can anyone help me with this?

Conversely, I'd like the same calculation in another cell

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

that adds exclusively counts when the values of
=Data!M1:M2500 values are "FA" or Recordable"

Thank you.

Hi

Try this:

=SUMPRODUCT(--(Data!E$1:E$2514=Lists!A13),--(Data!E$1:E$2514<Lists!B13),--(Data!K$1:K$2514="Other"),(AND(Data!$M$1:$M$2514< "FA")*(Data!$M$1:$M$2514<"Nonrecordable")))

and

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))-=SUMPRODUCT(--(Data!E$1:E$2514=Lists!A13),--(Data!E$1:E$2514<Lists!B13),--(Data!K$1:K$2514="Other"),(AND(Data!$M$1:$M$2514< "FA")*(Data!$M$1:$M$2514<"Nonrecordable")))

Regards,
Per



"Gina" skrev i meddelelsen
...
=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

I'd like to have this same number returned, but I'd like it to filter out
when

=Data!M1:M2500 does NOT equal "FA" or "Nonrecordable".

Can anyone help me with this?

Conversely, I'd like the same calculation in another cell

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

that adds exclusively counts when the values of
=Data!M1:M2500 values are "FA" or Recordable"

Thank you.

Sorry - my previous suggestion will not work for the < condition.

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(Data !M$1:M$2514<"FA")*(Data!M$1:M$2514<"Nonrecordabl e"))



"JMB" wrote:

try

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(Data !M$1:M$2514<{"FA", "Nonrecordable"}))

change < to = for the second part of your question.

"Gina" wrote:

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

I'd like to have this same number returned, but I'd like it to filter out
when

=Data!M1:M2500 does NOT equal "FA" or "Nonrecordable".

Can anyone help me with this?

Conversely, I'd like the same calculation in another cell

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

that adds exclusively counts when the values of
=Data!M1:M2500 values are "FA" or Recordable"

Thank you.

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(ISNA (MATCH(Data!M$1:M$2514,{"FA","Nonrecordable"},))))



"Gina" wrote:

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

I'd like to have this same number returned, but I'd like it to filter out
when

=Data!M1:M2500 does NOT equal "FA" or "Nonrecordable".

Can anyone help me with this?

Conversely, I'd like the same calculation in another cell

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

that adds exclusively counts when the values of
=Data!M1:M2500 values are "FA" or Recordable"

Thank you.

Thank you guys so much. It's always nice to know that when I get to the
point of banging my head into the wall, I can find help here. (You are the
ones who taught me how to use sumproduct in the first place!)

Gina

"Teethless mama" wrote:

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other")*(ISNA (MATCH(Data!M$1:M$2514,{"FA","Nonrecordable"},))))



"Gina" wrote:

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

I'd like to have this same number returned, but I'd like it to filter out
when

=Data!M1:M2500 does NOT equal "FA" or "Nonrecordable".

Can anyone help me with this?

Conversely, I'd like the same calculation in another cell

=SUMPRODUCT((Data!E$1:E$2514=Lists!A13)*(Data!E$1 :E$2514<Lists!B13)*(Data!K$1:K$2514="Other"))

that adds exclusively counts when the values of
=Data!M1:M2500 values are "FA" or Recordable"

Thank you.