--((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m"))
Since the range can contain only one or the other but not both at the same
time, the result of (array1)+(array2) can only be the numbers 1 or 0 so
there's no need to use the double unary on this particular array. The
additon of the arrays will corece to numeric 1 or 0.
--
Biff
Microsoft Excel MVP
"Ashish Mathur" wrote in message
...
Hi,
try this. Not tested
SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--((Data!$AC$2:$AC$10000="c")+(Data!$AC$2:$AC$10000= "m"))
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"Diddy" wrote in message
...
Hi everyone,
I'm using
=SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="c"))+SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(Data!$AC$2:$AC$10000="m"))
So it counts if C = a5, K = 10, I = M and AC = either c or m
Is there a better way to do it?
--
Diddy