Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
Help with SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct? | Excel Worksheet Functions | |||
sumproduct? | Excel Worksheet Functions |