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


    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


    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!

Participate now!

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