sumproduct
 

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.

sumproduct
 

one possibility
=sumproduct(--(H1:H1000O1:O1000),--or(C1:C1000="jw",C1:C1000="wh",C1:C1000="br",C1:C1 000="sm"))


"Harley" wrote:

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.

sumproduct
 

Hi

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

Note you cannot use whole column references in Sumproduct.
Adjust range to suit.
--
Regards
Roger Govier



"Harley" wrote in message
...
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.

sumproduct
 

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:H10020)*(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" wrote:

Hi

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

Note you cannot use whole column references in Sumproduct.
Adjust range to suit.
--
Regards
Roger Govier



"Harley" wrote in message
...
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.

sumproduct
 

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.

--
Regards
Roger Govier



"bj" wrote in message
...
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:H10020)*(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" wrote:

Hi

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

Note you cannot use whole column references in Sumproduct.
Adjust range to suit.
--
Regards
Roger Govier



"Harley" wrote in message
...
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.