COUNTIF With Multiple Criteria Across Multiple Columns

[GymWrecker]

Need help with the below formula. Not sure what the problem is since its returning 0 when it should return 2 since there are two rows that meet the four criterias that I'm looking for (W85NAA, OD, 3A., and MSG). Im using this as an example since I have a huge list with all the criterias below.

B1=W85NAA
B2=W73P01
B3=W9G9AA
B4=WSR7AA

K1=DUE
K2=OD

U1=1A. Pending Separation
U2=2A. Discharged
U3=3A. Pending Loss
U4=4A. Medical
U5=5A. Transfer

E1=CSM
E2=SGM
E3=MSG
E4=SFC
E5=SSG
E6=SGT

A1=SUMPRODUCT(COUNTIFS(Master!$B$1:$B$4,"*W85NAA*" ,Master!$K$1:$K$2,"*OD*",Master!$U$1:$U$5,{"*1A*", "*2A*","3A*","4A*","5A*","6A*","7A*"},Master!$E$1: $E$6,{"SGM";"CSM";"1SG";"MSG";"SFC";"SSG";"SGT"}))

[Excel Fox]

W85NAA is in the 1st Row
OD is in the 2nd Row
3A. and MSG is in the 3rd Row

How do you say that you should get 2 as result?

[GymWrecker]

Excel Fox, here's the actual spreadsheet. Under the Tab Summary you can find the formula(s) that I need help with (highlighted in yellow). Hope this helps.

https://app.box.com/s/xgnkmsc89871n6gaf6st

[Excel Fox]

Try this one

=SUMPRODUCT(ISNUMBER(SEARCH("W7ZPB1",Master!$B$1:$ B$300))*ISNUMBER(SEARCH("OD",Master!$K$1:$K$300))* ISNUMBER(SEARCH("9",Master!$U$1:$U$300))*ISNUMBER( SEARCH({"MG","BG","COL","LTC","MAJ","CPT","1LT","2 LT","CW4","CW3","CW2","WO1"},Master!$E$1:$E$300) ))

I've used this from cell F19

[GymWrecker]

Excel Fox, the formula still missing one more criteria: Master!$U$1:$U$300,{"*1A*","*2A*","3A*","4A*","5A* ","6A*","7A*"}, I think once I get this criteria embeded into the formula it should work.

=SUMPRODUCT(ISNUMBER(SEARCH("W7ZPB1",Master!$B$1:$ B$300))*ISNUMBER(SEARCH("OD",Master!$K$1:$K$300))* ISNUMBER(SEARCH("9",Master!$U$1:$U$300))*ISNUMBER( SEARCH({"MG","BG","COL","LTC","MAJ","CPT","1LT","2 LT","CW4","CW3","CW2","WO1"},Master!$E$1:$E$300) ))

Thanks again!

[GymWrecker]

My bad, I see that the range U1:U300 is in the formula. The only problem is that instead of "9", it should have the following criteria:*1A*","*2A*","3A*","4A*","5A* ","6A*","7A*

[Excel Fox]

So in place of "9", use {"1A","2A","3A","4A","5A","6A","7A"}

[Excel Fox]

Or even better, use {"1A.","2A.","3A.","4A.","5A.","6A.","7A."}

[GymWrecker]

Well, I replaced "9" with the right criteria, but I'm now getting an error "N/A"

This is the formula I have in
F19=SUMPRODUCT(ISNUMBER(SEARCH("W7ZPB1",Master!$B$ 1:$B$300))*ISNUMBER(SEARCH({"*1A.*","*2A.*","*3A.* ","*4A.*","*5A.*","*6A.*","*7A.*"},Master!$K$1:$K$ 300))*ISNUMBER(SEARCH({"MG","BG","COL","LTC","MAJ" ,"CPT","1LT","2LT","CW4","CW3","CW2","WO1"},Master !$E$1:$E$300)))

[Excel Fox]

Please remove the asteriks. You don't need that.