We help IT Professionals succeed at work.

How to I change SUMPRODUCT to see multiple criteria

Lazarus
Lazarus asked
on
I have a formula that works, but I need to add some criteria, and I've reached my limits.
This formula:
=SUMPRODUCT(--('DMS Credential '!A:A=A3),--('DMS Credential '!J:J="AB"))
It now needs to now also look for all of these instead of just "AB":
PASS AB
PASS AE
PASS OB-GYN
PASS RVT

How can I make that work?
Comment
Watch Question

Top Expert 2015

Commented:
The best way to do this is to create a table arrangement for all the count of which you want to do ..and then using that as a reference point to match the values that you want to count...

Like this..

=SUMPRODUCT((A1:A24=A3)*(ISNUMBER(MATCH(J1:J24,F3:F7,0))))

Where in F3:F7 is my input criteria of items which i want count of...

Refer to enclosed workbook for more details...

Saurabh...
sumproduct.xlsx
Saurabh's method is definitely the way to go if you need to change the parameters often. If not, you can use a formula like this....

=SUMPRODUCT(('DMS Credential '!A:A=A3)*('DMS Credential '!J:J={"AB", "PASS AB", "PASS AE", "PASS OB-GYN", "PASS RVT"}))

Wayne

Author

Commented:
Perhaps I should have attached the spreadsheet so you could see why it is the way it is. The two above formula's will not work with what I'm doing that I can see as of yet. I've attached the file to help you help me, thank you
TEMPLATE-DMS-Tracking.xlsm
I don't really understand your requirement. Which cell contains the above formula? Based on the workbook you provided, what is it you are trying to achieve?

Author

Commented:
in the Workbook, the Counts sheet column M, It counts from the DMS Credential sheet. It counts all the rows assigned to each class ID in that sheet.
Most Valuable Expert 2013
Commented:
In M2 you have this formula

=SUMPRODUCT(--('DMS Credential '!A:A=A2),--('DMS Credential '!J:J="PASS"))

I would recommend COUNTIFS as a better, faster option, i.e.

=COUNTIFS('DMS Credential '!A:A,A2,'DMS Credential '!J:J,"PASS")

and then for multiple criteria in column J try this version

=SUMPRODUCT(COUNTIFS('DMS Credential '!A:A,A2,'DMS Credential '!J:J,{"AB","PASS AB","PASS AE","PASS OB-GY","PASS RVT"}))

In that version COUNTIFS is still doing most of the work, SUMPRODUCT just sums the small array returned by COUNTIFS

regards, barry

Author

Commented:
Barry, That's what I needed... Thank you.