• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 38
  • Last Modified:

How to I change SUMPRODUCT to see multiple criteria

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":

How can I make that work?
1 Solution
Saurabh Singh TeotiaCommented:
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..


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

Refer to enclosed workbook for more details...

Wayne Taylor (webtubbs)Commented:
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"}))

LazarusAuthor 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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Wayne Taylor (webtubbs)Commented:
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?
LazarusAuthor 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.
barry houdiniCommented:
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
LazarusAuthor Commented:
Barry, That's what I needed... Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now