I had no luck with embedding IF into your formula
I used a helper column (C) with =IF(B1="a",A1,"")
Then I used =SUM(1/COUNTIF(C1:C10,C1:C10)) - 1
The subtraction of 1 is for the empty cells
Column C can be hidden or use a column to the far right.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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