counting distinct entries with a qualification

[Stephen]

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

 

[muddan madhu]

try this

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

use ctrl +shift + enter

 

[Mike H]

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:A10,A1:A10,0)),ROW(INDEX(A1:A10,0,0))-ROW(A1)+1))

Array formula are entered using CTRL+Shift+Enter.

Mike

 

[Bernard Liengme]

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

 

[Peo Sjoblom]

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

 

[ShaneDevenshire]

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)

 

[Peo Sjoblom]

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

 

[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