Here's another


=SUM(N(FREQUENCY(IF(B2:B20="a",MATCH(A2:A20,A2:A20 ,0)),MATCH(A2:A20,A2:A20,0))0))

entered with ctrl + shift & enter

--


Regards,


Peo Sjoblom

"Stephen" <none wrote in message
...
I know about the formula
=SUM(1/COUNTIF(A1:A10,A1:A10))
for counting the number of distinct (text) entries in the range A1:A10
(where there are no blanks).

What I need is a worksheet formula to do a similar job, but only taking
into consideration rows where B1:B10 contain "a".
So, the following data would give a result of 4.
John a
Fred a
Sally
Jane
Jane a
John a
Fred
Alan a
Tim
Alan a

Any suggestions, please?

Stephen