counting distinct numbers

  • Thread starter Thread starter Smudger
  • Start date Start date
S

Smudger

OK - This one has been bugging me for a while now.
I want to count the number of 'distinct' numbers in a
column. eg 1,2,3,1 each in a separate column - should
return 3(3 distinct numbers). I can do this by adding
columns and using "=COUNTIF(A$1:A6,A6)" and then summing
with "=COUNTIF(B1:B8,1)" but i can't get excel to do this
in a 'One cell count function' - I am getting well
stressed with this - PLEASE HELP

Smudger
 
Smudger,

This formula counts unique numeric entries, that is it ignores any text in
the data

=SUM(IF(FREQUENCY(A1:A20,A1:A20)>0,1,0))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
with your data in cells A3:A12
enter into any cell, say B3
=SUM(1/COUNTIF(A3:A12,A3:A12)) << Do not press Enter INSTEAD

Press Control + (plus) + Shift + Enter
After doing so the formula bar should show
B3 as {=SUM(1/COUNTIF(A3:A12,A3:A12))} as this is an array entered
formula.
HTH
 
Back
Top