# Sumproduct formula with Multiple Criteria help

#### Malleshg24

##### New Member
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

#### Attachments

• 20.3 KB Views: 4

#### Hui

##### Excel Ninja
Staff member
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

#### Attachments

• 26.6 KB Views: 4