Hi,

Here is another array entered solution:

=SUM(1/(IF(B1:B10="a",1,10^10)*COUNTIF(A1:A10,A1:A10)))

and technically this returns 4.00000000005 for your data so you could apply
the round function:

=ROUND(SUM(1/(IF(B1:B10="a",1,10^10)*COUNTIF(A1:A10,A1:A10))),2 )

--
Thanks,
Shane Devenshire


"Stephen" wrote:

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