Forum Discussion
Solved
COUNTIF text in another cell is unique to its column.
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 in more than one row. Excel says I am missing an opening or closing parenthesis but when I insert, Excel 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
In addition to array formula with FREQUENCY, regular one could be
=SUMPRODUCT( (C5:C166 <> "")*(I5:I166 = "Eng")/COUNTIFS(C5:C166,C5:C166 & "", I5:I166, I5:I166 & ""))on Excel 365
=COUNTA(UNIQUE(FILTER(C5:C166,I5:I166 = "Eng")))
4 Replies
In addition to array formula with FREQUENCY, regular one could be
=SUMPRODUCT( (C5:C166 <> "")*(I5:I166 = "Eng")/COUNTIFS(C5:C166,C5:C166 & "", I5:I166, I5:I166 & ""))on Excel 365
=COUNTA(UNIQUE(FILTER(C5:C166,I5:I166 = "Eng")))- Once again, MANY THANKS!
MMHiggins , you are welcome, glad it helped
