More than 2 CountIf/SumProduct criteria

[ajhubble]

OK, from reading through the previous threads on countif and sumproduct
I have this question: I'm trying to count the number of rows which hav
3 matching criteria from 2 columns. My formula needs to count th
number of rows in column C:C which match either "Test1" or "Test2" an
to count only if column L:L has a "Y".

This expression works:
=SUMPRODUCT(--(Sheet1!C1:C165="Test1"), --(Sheet1!L1:L165="Y"))

But this expression doesn't (#VALUE):
=SUMPRODUCT(OR(--(Sheet1!C1:C165="Test1"),--(Sheet1!C1:C165="Test2")),--(Sheet1!L1:L165="Y"))

Neither does this (#VALUE):
=SUMPRODUCT(--OR((Sheet1!C1:C165="Test1"),(Sheet1!C1:C165="Test2")),--(Sheet1!L1:L165="Y"))

How do I specify more than 1 possible match for the any particula
column?

And second question: how do I specify the function to search the entir
used range of the column? It can't seem to take "C:C" as a valid colum
(despite it being able to work for nearly every other workshee
function). I suppose I can do "C1:C65000", but it does seem inelegan
and I don't want Excel calculating all the way down there if it doesn'
need to. Excel should know how far down the spreadsheet goes and shoul
only calculate down to the last used row.

Suggestions? Thanks

 

[JE McGimpsey]

One way:

This is a time when using the * operator before SUMPRODUCT makes sense,
since the first term will return a 165x2 array, which SUMPRODUCT can't
multiply by a 165x1 array. However, if the multiplication happens before
SUMPRODUCT sees the array, it just has to sum the result of the
multiplication:

=SUMPRODUCT((Sheet4!C1:C165={"Test1","Test2"})*(Sheet4!L1:L165="Y"))

You can't use OR() since it returns a single value rather than an array.

 

[RagDyer]

Granted yours is shorter and more elegant John, but this is perhaps what the
OP was searching for with his attempts, at the outset:

=SUMPRODUCT(--(C1:C165="test1")+(C1:C165="test2"),--(L1:L165="Y"))
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


One way:

This is a time when using the * operator before SUMPRODUCT makes sense,
since the first term will return a 165x2 array, which SUMPRODUCT can't
multiply by a 165x1 array. However, if the multiplication happens before
SUMPRODUCT sees the array, it just has to sum the result of the
multiplication:

=SUMPRODUCT((Sheet4!C1:C165={"Test1","Test2"})*(Sheet4!L1:L165="Y"))

You can't use OR() since it returns a single value rather than an array.

 

[Peo Sjoblom]

Can be shortened to

=SUMPRODUCT((C1:C165="test1")+(C1:C165="test2"),--(L1:L165="Y"))

 

[JE McGimpsey]

Perhaps. No need to use the double unary minuses in the first term
though, since you're using the + operator which will cause the coercion
to numbers.

 

[RagDyer]

Which is probably why I never use it, but stick to the way I first learned
it, just using "*".

=SUMPRODUCT(((C1:C165="test1")+(C1:C165="test2"))*(L1:L165="Y"))

Easier for *me* to understand, although your use of the array constants is
much prettier.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------



Perhaps. No need to use the double unary minuses in the first term
though, since you're using the + operator which will cause the coercion
to numbers.

 

[ajhubble]

Wow, this is exactly what I was looking for. And it all works. Thanks
lot for the help