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

try this

=SUMPRODUCT(--(RIGHT(A1:A10,1)="a"))-(COUNTA(A1:A10)-SUM(1/
COUNTIF(A1:A10,A1:A10)))

use ctrl +shift + enter

On Sep 29, 11:29*am, "Stephen" <none 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

I'm sure this is dooable with a non-array sumproduct but in the meantime try
this array formula

=COUNT(1/FREQUENCY(IF((B1:B10="a")*(B1:B10<""),MATCH(A1:A1 0,A1:A10,0)),ROW(INDEX(A1:A10,0,0))-ROW(A1)+1))

Array formula are entered using CTRL+Shift+Enter.

Mike

"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

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

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

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

It doesn't work.

Try it with these values in A1:B10



2 a
2 b
2 c
2 a
2 b
2 c
2 a
2 b
2 c
2 a


It should return 1 but it returns 0.4

--


Regards,


Peo Sjoblom

"ShaneDevenshire" wrote in
message ...
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

Thanks to all who offered solutions, particularly Mike H and Peo Sjoblom
whose formulas worked well. The latter is shorter, whilst the former copes
with blanks in column A. It's great to get such good quality help. This is
much appreciated.

Thanks,
Stephen

"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