Forum Discussion
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 there are 2:
=COUNT(K5:K166,"P",(COUNTIF(H5:H166,"PT")))
Help appreciated. Thank you.
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).
5 Replies
- That worked, Sergei. THANK YOU. I thought that was one of the first I tried but obviously not!
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
