Count duplicates where the next column contains specific text

A

ar0618

I need to write a formula which counts the number of unique entries i
columnA where the corresponding entries in columnB contain a specifi
string of text (ie *text*).

Can anyone help with an answer to this problem or is it impossible t
do in excel
 
B

Bob Phillips

=SUM(--(FREQUENCY(IF(B2:B100="text",MATCH(A2:A100,A2:A100,0)),ROW(INDIRECT("
1:"&ROWS(A2:A100))))>0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Top