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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
You'll want to enter the following formula and press control-shift-enter (same time) once it's in the formula bar:

=SUM((A1:A65536=1)*(B1:B65336="pass"))

Should look like this:

{=SUM((A1:A65536=1)*(B1:B65336="pass"))}

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-19 15:14
 
Upvote 0
On 2002-03-19 15:00, jereece wrote:
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

Given that the sample you provided (with labels added)

{"Studuent","Result";1,"pass";1,"fail";1,"pass";2,"pass";1,"pass";1,"fail";2,"pass"}

is in A1:B8,

you can also use:

=SUMPRODUCT((A2:A8=1)*(B2:B8="pass"))

where, if desired, 1 and pass can be in cells of their own and their cell refs then can be used in the formula.

Another way would be to use DCOUNT:

=DCOUNT(A1:B8,1,D1:E2)

where D1 houses the label 'Student', D2 the criterion/condition 1, E1 the label 'Result', and E2 the criterion/condition "pass" (without double quotes").

Aladin
This message was edited by Aladin Akyurek on 2002-03-19 15:57
 
Upvote 0
Thanks. The only one I could get to work correctly was the SUMPRODUCT, but it works great.

Thanks again.
Jim
 
Upvote 0
If you take the formula from Nate and correct the type o it works fine.....

=SUM((A1:A65336=1)*(B1:B65336="pass"))
 
Upvote 0
When I use =SUM((A1:A65336=1)*(B1:B65336="pass")), I get a result of 1 when clearly I have several that match.


=SUMPRODUCT((A2:A21=1)*(B2:B21="pass")) still works however.

Thanks,
Jim
 
Upvote 0
First - do you really need to look at 65536 rows?

SUMPRODUCT instead of SUM, and shorten the range -- SUMPRODUCT does not like whole column arrays.
 
Upvote 0
jereece said:
When I use =SUM((A1:A65336=1)*(B1:B65336="pass")), I get a result of 1 when clearly I have several that match.

The range in A must be numeric and the range in B should not have any spaces or non-visible chars around the values it houses. Try to run ASAP Utilities or the TrimAll macro (available in some threads on this site) on the ranges of interest to exclude that problem.
 
Upvote 0
=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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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