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%

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%

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%

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%