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":
PASS AB
PASS AE
PASS OB-GYN
PASS RVT

How can I make that work?
LVL 20
LazarusAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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..

=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
0
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"}))

Wayne
0
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
TEMPLATE-DMS-Tracking.xlsm
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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?
0
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.
0
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LazarusAuthor Commented:
Barry, That's what I needed... Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.