counting distinct entries with a qualification

  • Thread starter Thread starter Stephen
  • Start date Start date
S

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
 
try this

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

use ctrl +shift + enter
 
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
 
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
 
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
 
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)
 
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
 
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
 
Back
Top