| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct including empty cells
This formula works except when a different column has data in it.
SUMPRODUCT(--(M2:M10="b"),--(C2:C10<100),--(C2:C1050))/COUNTIF(M2:M10,"b") c d m 75 b 200 b 25 b It returns 33% instead of 50% |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct including empty cells
So, are you saying that you want something like this:
=SUMPRODUCT(--(M2:M10="b"),--(C2:C10<100),--(C2:C1050))/SUMPRODUCT(-- (M2:M10="b"),--(C2:C10<"")) ? Hope this helps. Pete On Jan 17, 1:54*am, Rene wrote: This formula works except when a different column has data in it. SUMPRODUCT(--(M2:M10="b"),--(C2:C10<100),--(C2:C1050))/COUNTIF(M2:M10,"b") c * * * * d * * * * * * * *m 75 * * * * * * * * * * * * b * * * * * 200 * * * * * * b 25 * * * * * * * * * * * * b It returns 33% instead of 50% |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct including empty cells
33% look like the correct answer to me. One row satisfies the condition for
column C; all three rows satisfy the condition for column M; one divided by 3 equals 33%. -- David Biddulph "Rene" wrote in message ... This formula works except when a different column has data in it. SUMPRODUCT(--(M2:M10="b"),--(C2:C10<100),--(C2:C1050))/COUNTIF(M2:M10,"b") c d m 75 b 200 b 25 b It returns 33% instead of 50% |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sumproduct including empty cells
However, if column C is empty; ignore that row in the computation. Thanks
for your input. "David Biddulph" wrote: 33% look like the correct answer to me. One row satisfies the condition for column C; all three rows satisfy the condition for column M; one divided by 3 equals 33%. -- David Biddulph "Rene" wrote in message ... This formula works except when a different column has data in it. SUMPRODUCT(--(M2:M10="b"),--(C2:C10<100),--(C2:C1050))/COUNTIF(M2:M10,"b") c d m 75 b 200 b 25 b It returns 33% instead of 50% |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
| When I SUM cells & 1 is empty I need the result to be empty not 0 | Excel Discussion (Misc queries) | |||
| How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) | |||
| Pivot Tables: How do I show ALL field rows, including empty rows?? | Excel Worksheet Functions | |||
| Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) | |||
Linear Mode
