• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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
63528

See attached file
 

Attachments

Top