Multiple Count IFs

[jereece]

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

[NateO]

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, NateO

[ This Message was edited by: NateO on 2002-03-19 15:14 ]

[Aladin Akyurek]

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 ]

[jereece]

Thanks. The only one I could get to work correctly was the SUMPRODUCT, but it works great.

Thanks again.
Jim

[kinkyparamour]

If you take the formula from Nate and correct the type o it works fine.....

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

[NateO]

Nice catch Kinky, I thought I lined both ranges up but no such luck.

[jereece]

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

[just_jon]

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.

[Aladin Akyurek]

Originally Posted by jereece

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.

[jereece]

=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