Is there a better way?

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?
 
A

Ashish Mathur

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
 
M

Max

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
 
D

Diddy

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
 
D

Diddy

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
 
F

francis

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
 
T

T. Valko

--((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.
 
D

Diddy

Thank you Biff,

I'm determined to understand SUMPRODUCT one day and each bit of info helps :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add criteria to sumproduct Greater than or equal to 5
Sumproduct neither nor 6
add two criteria that should not be met to sumproduct 3
add two criteria that should not be met to sumproduct 4
How to Average a Sumproduct() formula 4
Formula Array Help in VBA 3
Sumif with an array for criteria 3
Adding another if statement to an index formula 0

Top