sumproduct

[Guest]

I need to count the number of times column H is greater than 0, when column C
= jw, wh, br or sm. Can't seem to work it out.

 

[Guest]

one possibility
=sumproduct(--(H1:H1000>O1:O1000),--or(C1:C1000="jw",C1:C1000="wh",C1:C1000="br",C1:C1000="sm"))

 

[Roger Govier]

Hi

Try
=SUMPRODUCT(--(H1:H1000>0),--(C1:C1000={"jw,"wh","br","sm"}))

Note you cannot use whole column references in Sumproduct.
Adjust range to suit.

 

[Guest]

even after adding the second quote mark around jw, (you are starting to type
like I do) I still get an #value error using your equation
I can do either section and get an answer, but not in the format you have it.
when I use
=SUMPRODUCT((H1:H1002>0)*(C1:C1002={"jw","wh","br","sm"})),
which should be identical, I get the right answer
I have 2003
does anyone else have this problem?

 

[Roger Govier]

Hi

Sorry about the missing quotes.
Yes, this is one of those occasions where using * rather than -- and ,
works, and not vice versa.
I think it is because the coercion from True/False to 1/0 only takes place
when the terms are multiplied, as opposed to the coercion taking place on
each term through the double unary minus, before the sumproduct calculation
occurs.

This is consistent through all versions of Excel.