Forum Discussion
Need Help: Count a Cell Equals Exact Text ONLY IF another Cell Equals Exact Text
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).
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.
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).