 # Countif 3 Conditions

• I have two sheets, 'Summary' and 'Attendance Log'. On both sheets \$A\$3:\$A\$65536 is a list of Last Names, and \$B\$3:\$B\$65536 is a list of first names. In cell F3 on 'Summary' I want the number of cells in 'Attendance Log'!\$F\$3:\$IV\$65536 which contain the letter P and are in the same row as a Last and First name (in column A and B of 'Attendance Log' respectively) which match those names in \$A3 and \$B3 on 'Summary' respectively.

Thanks

• Re: Countif 3 Conditions

Quote from wildearth2001

In cell F3 on 'Summary' I want the number of cells in 'Attendance Log'!\$F\$3:\$IV\$65536 which contain the letter P...

I'll give you this much.
=COUNTIF('Attendance Log'!F3:IV65536,"P")

The rest sounds too confusing to contemplate.

• Re: Countif 3 Conditions

Wildearth, From your previous thread, I see that you had entered a formula which makes me wonder whether these cells are going to have "P" in them or perhaps "xxxPxxx". Can you clarify whether a wildcard search is needed, please?

• Re: Countif 3 Conditions

Quote from ByTheCringe2

Wildearth, From your previous thread, I see that you had entered a formula which makes me wonder whether these cells are going to have "P" in them or perhaps "xxxPxxx". Can you clarify whether a wildcard search is needed, please?

Yes a wildcard is neccessary, as some cells will contain multiple codes. The formula I entered in my other thread works just fine if the names are in the same order on both sheets, but now Im trying to be able to have the names in any order and have it still work. Thanks

• Re: Countif 3 Conditions

Try:

=SUMPRODUCT(('Attendance Log'!A\$3:A\$65536=\$A\$3)*('Attendance Log'!B\$3:B\$65536=\$B\$3)*(ISNUMBER(FIND("P",'Attendance Log'!\$E3:\$J3))))

• Re: Countif 3 Conditions

Quote from ByTheCringe2

Try:

=SUMPRODUCT(('Attendance Log'!A\$3:A\$65536=\$A\$3)*('Attendance Log'!B\$3:B\$65536=\$B\$3)*(ISNUMBER(FIND("P",'Attendance Log'!\$E3:\$J3))))

Not quite, when I inserted that formula it seams to only check IF there is a value in column A that matchs, AND IF there is a value in Column B that matches and if both of those are true it counts how many P are in row 3, but not necessarily the same row as the name.

• Re: Countif 3 Conditions

This?

=(AND(A3='Attendance Log'!A3,B3='Attendance Log'!B3)*COUNTIF('Attendance Log'!\$E3:\$CP3,"*P*"))

• Re: Countif 3 Conditions

Nothing presented so far works. Ive been playing around a little myself and also cannot find anything.

• Re: Countif 3 Conditions

The last one worked here. Attach some real data and I'll get something to work.

• Re: Countif 3 Conditions

Since you're using Excel 2003, first convert your data into a list...

Data > List > Create List

Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

Code
``=SUM(IF('Attendance Log'!\$A\$3:\$A\$10=Summary!\$A3,IF('Attendance Log'!\$B\$3:\$B\$10=Summary!\$B3,IF(INDEX('Attendance Log'!\$F\$3:\$Q\$10,MATCH(1,IF('Attendance Log'!\$A\$3:\$A\$10=Summary!\$A3,IF('Attendance Log'!\$B\$3:\$B\$10=Summary!\$B3,1)),0),0)="P",1))))``

Use the actual ranges for your exisiting data. The ranges will automatically adjust as data is added.

Hope this helps!

• Re: Countif 3 Conditions

This seams to work great other than I need to to do a wildcard search. Im interested in cells which contain P, but they may also have other letters. I tried just adding the ** to the code you gave but that didnt work.

• Re: Countif 3 Conditions

Try...

=SUM(IF( 'Attendance Log'!\$A\$3:\$A\$10=Summary!\$A3,IF('Attendance Log'!\$B\$3:\$B\$10=Summary!\$B3,IF(ISNUMBER(SEARCH("P",'Attendance Log'!\$F\$3:\$Q\$10)),1))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

• Re: Countif 3 Conditions

For convenience:

=SUM(IF('Attendance Log'!\$A\$3:\$A\$10=Summary!\$A3,IF('Attendance Log'!\$B\$3:\$B\$10=Summary!\$B3,IF(INDEX('Attendance Log'!\$F\$3:\$Q\$10,MATCH(1,IF('Attendance Log'!\$A\$3:\$A\$10=Summary!\$A3,IF('Attendance Log'!\$B\$3:\$B\$10=Summary!\$B3,1)),0),0)="P",1))))

• Re: Countif 3 Conditions

Actually, my first formula should have been...

=COUNTIF(INDEX('Attendance Log'!\$F\$3:\$Q\$10,MATCH(1,IF('Attendance Log'!\$A\$3:\$A\$10=Summary!\$A3,IF('Attendance Log'!\$B\$3:\$B\$10=Summary!\$B3,1)),0),0),"P")

My second formula could be made more efficient...

=COUNTIF(INDEX('Attendance Log'!\$F\$3:\$Q\$10,MATCH(1,IF('Attendance Log'!\$A\$3:\$A\$10=Summary!\$A3,IF('Attendance Log'!\$B\$3:\$B\$10=Summary!\$B3,1)),0),0),"*P*")

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

• Re: Countif 3 Conditions

Your new second formula looks like its working perfect. THANKS!!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!