Sumproduct formula with Multiple Criteria help

[Malleshg24]

Hi Team,

Need your help in sumproduct formula, Below formula giving correct result. Three Columns and Three Criteria
Range("F12").Formula = Evaluate("=SUMPRODUCT(ISNUMBER(MATCH($A$1:$A$100,$E$2:$E$4,0))*ISNUMBER(MATCH($B$1:$B$100,$F$2:$F$4,0))*ISNUMBER((MATCH($C$1:$C$100,$G$2:$G$6,0))))")

Third Column(C) Contains Numbers, I want the No Count result in group.

between 0-5 in Range F12.
6-10 in Range G12,
11-15 in Range H12,
>15 in Range I12.

How to add one more Criteria in below formula, So that It will give numbers Counts as well.
Evaluate("=SUMPRODUCT(ISNUMBER(MATCH($A$1:$A$100,$E$2:$E$4,0))*ISNUMBER(MATCH($B$1:$B$100,$F$2:$F$4,0))


Regards,
Mallesh

 

[Hui]

Mallesh

It is not overly clear what you want

I assume you want a formula to count according to the criteria at the top, but these are inconsistent in length
They also don't include all the values in Column A

Have you also considered using Countifs?

Criteria 1 and 2 and 3 between 1 and 5
=COUNTIFS($A$2:$A$100,$E2,$B$2:$B$100,$F2,$C$2:$C$100,">="&1,$C$2:$C$100,"<="&5)

Criteria 1 and 2 and 3 > 15
=COUNTIFS($A$2:$A$100,$E2,$B$2:$B$100,$F2,$C$2:$C$100,">="&15)

Secondly do you need to use VBA ?

Finally have you considered a Pivot Table

I added a new field in Column D, Criteria
Then in D2: =CHOOSE(INT(C2/5)+1,"1-5","6-10","11-15",">15",">15")
copy that down

Then setup a Pivot Table


See attached file