Forum Discussion

MMHiggins's avatar
MMHiggins
Copper Contributor
Aug 21, 2021
Solved

Need Help: Count a Cell Equals Exact Text ONLY IF another Cell Equals Exact Text

Hello,   I am trying to count cells in column "K" with a text value of "P" ONLY IF cells in column "H" have a text value of "PT." This is my formula but it keeps returning the value "1" when I know...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Aug 21, 2021

    MMHiggins 

    With FREQUENCY() that could be

    =SUM(  --( FREQUENCY( IF( LEN(C5:C166)>0, IF( I5:I166="Eng", MATCH(C5:C166,C5:C166,0))),  ROW(C5:C166) - ROW(C5) +1 ) >0))

    If you are not on Excel 365 you shall use above formula as array one. i.e. use Ctrl+Shift+Enter instead of Enter.  Such formula is explained here Excel formula: Count unique text values with criteria | Exceljet

     

    If you are on Excel 365 there are more simple ways to do the same, above formula shall be used as regulars one (just Enter).

MMHiggins's avatar
MMHiggins
Copper Contributor
Aug 21, 2021
That worked, Sergei. THANK YOU. I thought that was one of the first I tried but obviously not!
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 21, 2021

MMHiggins , you are welcome.

 

What your initial formula do:

=COUNT(K5:K166,"P",(COUNTIF(H5:H166,"PT"))) goes to

=COUNT(K5:K166,"P",5) assuming COUNTIF returns 5

COUNT() counts numbers in values presented by arguments ignoring the rest. Thus that is

0 (zero numbers in K5:K166, all texts) + 0 ("P" is text) + 1 (5 is number) = 1

  • MMHiggins's avatar
    MMHiggins
    Copper Contributor
    Aug 21, 2021

    SergeiBaklan This is a bit above my skill level to understand but thank you for explaining. Could you please help with one more problem? I am trying to count the number of persons who speak English (Column "I") but only if they are unique (Column "C"). Some persons are listed more than one row. This too is not working for me. Can you possibly solve? Excel says I am missing an opening or closing parenthesis but when I insert it then says I have too many arguments.

     

    =COUNTIFs(I5:I166,"Eng",(SUM(IF(FREQUENCY(IF(LEN(C5:C166)>0,MATCH(C5:C166,C5:C166,0),""),IF(LEN(C5:C166)>0,MATCH(C5:C166,C5:C166,0),""))>0,1))

     

    Thank you again.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 21, 2021

      MMHiggins 

      With FREQUENCY() that could be

      =SUM(  --( FREQUENCY( IF( LEN(C5:C166)>0, IF( I5:I166="Eng", MATCH(C5:C166,C5:C166,0))),  ROW(C5:C166) - ROW(C5) +1 ) >0))

      If you are not on Excel 365 you shall use above formula as array one. i.e. use Ctrl+Shift+Enter instead of Enter.  Such formula is explained here Excel formula: Count unique text values with criteria | Exceljet

       

      If you are on Excel 365 there are more simple ways to do the same, above formula shall be used as regulars one (just Enter).

Resources