count the number of unique entries in a column; with a certain filter

  • Thread starter Thread starter chvandewiele
  • Start date Start date
C

chvandewiele

if in column a I have a list of values,
I can get the number of unique values with:

{=SUM(1/COUNTIF(A1:A10;A1:A10))}

But, in column b I have a list of parameters to match (e.g
a,b,c,d,e,...)
Now, I want this number of unique values FOR EACH OF the parameters i
column b.

Can someone help?
 
=SUM(N(FREQUENCY(A:A,A:A)>0))

would give you the uique entries in column a
Randall
 
=SUM(N(FREQUENCY(A:A,A:A)>0))

would give you the uique entries in column a
Click to expand...
...

True, but the OP already appears familiar with the alternative of using
SUMPRODUCT(1/COUNTIF(Rng,Rng)), which handles text as well as numbers. Your
formula does nothing at all to address the OP's question regarding filtering.
...
 
try the following
=SUMPRODUCT((1/COUNTIF(A1:A10,A1:A10))*(B1:B10="a"))
Click to expand...
...

Apparently not tested. Consider the following in A1:B20, with col A values
corresponding to col B values of "A" highlighted.

G B
B A <--> B
B D
C B
F B
B E
D C
J A <--> J
E C
H C
H D
H F
F A <--> F
F E
J A <--> J
G E
E E
E C
B C
H E

The formula

=SUMPRODUCT((1/COUNTIF(A1:A20,A1:A20))*(B1:B20="A"))

returns 1.583333333. The correct answer is 3. COUNTIF can't accomodate filtered
ranges. You need something like

=SUM(1/MMULT(--(IF(B1:B20=B22,A1:A20)=TRANSPOSE(IF(B1:B20=B22,A1:A20))),
ROW(A1:A20)^0))-1
 
"Frank Kabel" wrote...
Click to expand...

Hi Harlan
I tested the formula but not apparently not enough. My test data always
produced the correct answer :-(
But you're right - it does not work in most circumstances - I was just
lucky in my test scenarios
thanks for the correction
Frank
 
The formula below seems to work fine!! Thank you very much!!!
Its a great help!!!!!





=SUM(1/MMULT(--(IF(B1:B20=B22,A1:A20)=TRANSPOSE(IF(B1:B20=B22,A1:A20))),
ROW(A1:A20)^0))-
 
Back
Top