SUMPRODUCT with COUNTIFS

I have a formula that works, but I've had to alter the overall way things work due to speed issues. The old formula:

=SUMPRODUCT(COUNTIFS('DMS Credential '!A:A,A2,'DMS Credential '!L:L,{"PASS AB","PASS ECHO","PASS OB-GYN","PASS RVT"}))

I've added a name range: fm_6 to better handle the amount of rows. The formulas there is:

=OFFSET('DMS Credential '!\$M\$2,0,0,COUNTA('DMS Credential '!\$A:\$A)-1,1)

I can't seem to figure out how to get the formula that I started with to work properly with the COUNTIFS
Normally in a cell if I only had one possible answer I would just enter:

=SUMPRODUCT((fm=A2)*(fm_6="PASS AB"))

But I'm still trying to learn these more complicated formulas, any help would be great. Thanks
TEMPLATE-DMS-Tracking-20150618.xlsm
LVL 20
Who is Participating?

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.

Commented:
You can use this formula to get what you are looking for...

=SUMPRODUCT((fm=A2)*((fm_6="PASS AB")+(fm_6="PASS ECHO")+(fm_6="PASS OB-GYN")+(fm_6="PASS RVT")))

This will count all the values where FM=A2 and whenever fm_6 is either equal to Pass AB or Pass Echo or Pass OB-GYN or Pass-RVT

Saurabh...

Experts Exchange Solution brought to you by