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

[MMHiggins]

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 there are 2:

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

Help appreciated. Thank you.

[#2]

@MMHiggins 

That could be

=COUNTIFS(K5:K166,"P",H5:H166,"PT")

[#3]

That worked, Sergei. THANK YOU. I thought that was one of the first I tried but obviously not!

[#4]

@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

[#5]

@Sergei Baklan 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.

[#6 · Accepted solution]

@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).