Sumproduct if conditions are met

[mrsak87]

Hi,

I'm having one of those days where my brain just isn't getting it, so please can you help a brother out.

I've attached an example, using sumproduct to COUNT an array. Unfortunately it's not quite working. I've highlighted the cells yellow with the answer I'm getting and green with what it should be.

The idea is to COUNT how many rows have a value for the location based on if it's "Prelim" or "Classification".

For location V026 there 8 rows with a value greater than 0 in the "prelim" category, but because there are two columns the value I'm getting is 9
For location V026 there 4 rows with a value greater than 0 in the "classification" category, but because there are four columns the value I'm getting is 13

Would appreciate your help

Thanks

[Haluk]

Try this;

Cell M1:

Please Login or Register  to view this content.

Cell M2:

Please Login or Register  to view this content.

Change 0,001 to 0.001 according to your regional settings.

[mrsak87]

Thanks for responding.

I've condensed the sheet down a lot from the real example - it wouldn't be possible to break each column down i.e do D, E, F etc separately, sorry I should have stated this previously

[Haluk]

Then, try this:

Array formulas, that has to be confirmed by Ctrl+Shift+Enter

In cell M1:

Please Login or Register  to view this content.

In Cell M2:

Please Login or Register  to view this content.

Change 0,001 to 0.001 and/or ";" to "," depending on your regional settings.

[Haluk]

@mrsak87;

You've not responded my last message whether your problem is solved or not but, a sample file is also attached below...

[Bo_Ry]

Key Perlim, Classification in I1, I2
then
L2
=SUM((MMULT((B$3:G$28)*(B$1:G$1=I1),SEQUENCE(COLUMNS(B$3:G$28)))>0)*(A$3:A$28=J1))

or M2 calculate faster for 10,000 more rows and columns
=SUM(N(MMULT(--FILTER(FILTER(B$3:G$28,A$3:A$28=J1),B$1:G$1=I1),SEQUENCE(COUNTIFS(B$1:G$1,I1)))>0))

[mrsak87]

Guys!

Both these solutions are beautiful! Thanks as well for the headsup on a large data set - if I could award extra bonus points I would.

thanks again

[Haluk]

You're welcome...