sumproduct including empty cells

R

Rene

This formula works except when a different column has data in it.
SUMPRODUCT(--(M2:M10="b"),--(C2:C10<100),--(C2:C10>50))/COUNTIF(M2:M10,"b")
c d m
75 b
200 b
25 b


It returns 33% instead of 50%
 
P

Pete_UK

So, are you saying that you want something like this:

=SUMPRODUCT(--(M2:M10="b"),--(C2:C10<100),--(C2:C10>50))/SUMPRODUCT(--
(M2:M10="b"),--(C2:C10<>""))

?

Hope this helps.

Pete
 
R

Rene

However, if column C is empty; ignore that row in the computation. Thanks
for your input.
 
Top