Is there a better way?

  • Thread starter Thread starter Diddy
  • Start date Start date
D

Diddy

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?
 
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
 
Another alternative option
=SUMPRODUCT(--(Data!$C$2:$C$10000=$A5),--(Data!$K$2:$K$10000=10),--(Data!$I$2:$I$10000="M"),--(ISNUMBER(MATCH(Data!$AC$2:$AC$10000,{"c";"m"},0))))

Joy? Click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
 
Thank you Ashish,

It needed another ) at the end. So I didn't need to to repeat the criteria
for the other columns again. Interesting!

Thanks
 
Hi Max,

Yes it works beautifully. I'm going to attempt to work out how it does it
but it may be a long job. Food for thought!

Thanks again
 
Max,

I would say this is creative!

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis
 
--((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.
 
Back
Top