Multiple Count IFs

jereece

New Member
Joined
Mar 18, 2002
Messages
25
Lets say I have the following spreadsheet:

ColA ColB
1 pass
1 fail
1 pass
2 pass
1 pass
3 fail
2 pass

In another cell I want to count how many "1" in column A were "pass". I tried to write a count if statement, but it did not work. Here's what I tried: =((COUNTIF(A:A,"1"))*AND((COUNTIF(B:B,"pass")))). This only returns the number of 1s in collumn A.

Can anyone help with single formula?

Thanks,
Jim
 
jereece said:
=SUMPRODUCT((A2:A8=1)*(B2:B8="pass"))

This seems to work for me. One last question, is there a way to get this to work with wild cards (i.e. "*pass*")? I have one database I want to use this with and the entries are not 100% consistent. If I could use a wild card, that would eliminate my problem.

I tried for example =SUMPRODUCT((A2:A8=1)*(B2:B8,*pass*)) but keep getting error messages.

Can anyone help?

Thanks,
Jim

Try,

=SUMPRODUCT(--(ISNUMBER(SEARCH("*pass*",B1:B10))),--(A1:A10=1))

Sumproduct in itself does not accept wildcards
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
I have never seen the search term first ("Data!*pass*",B1:B10), is that correct?

Also, if the data is on a different tab, would it be

=SUMPRODUCT(--(ISNUMBER(SEARCH("Data!*pass*",B1:B10))),--(Data!A1:A10=1))
 
Upvote 0
jereece said:
I have never seen the search term first ("Data!*pass*",B1:B10), is that correct?

Also, if the data is on a different tab, would it be

=SUMPRODUCT(--(ISNUMBER(SEARCH("Data!*pass*",B1:B10))),--(Data!A1:A10=1))

No, that's not correct...

=SUMPRODUCT(--ISNUMBER(SEARCH("pass",Data!B1:B10)),--(Data!A1:A10=1))

You can drop *'s around the word pass because SEARCH is capable of locating the search string anywhere in the target string.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top