Count same cells in one colum

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I'm looking for a way / formula to count cells within one colum.
With COUNTA it is eaasy but not usefull for my situation.
The situation is:
A B C D
1 1
2 2
3 3
4 4
5 5
6 5
7 6
8 6
9 6
10 7
11 8
12 9
13 10
14 11
15 12
There are 15 row and in Colum A are numbers some of them are double or
triple the possibility that they would accurs more is there. When I
use COUNTA in colum A I will see that there are 15 cells / hits /
items now I don't want to count the double or triple items or more. So
actually there are 12 hits instead of 15 because number 5 is double
and number 6 is triple.
I hope somebody can help me with this.
Thank you in advange.
 
Alex,
If every cell is populated, you can use

=SUM(1/COUNTIF(A1:A5,A1:A5))

If there migt be some blanks in there, use

=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A
100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))

Both are array formulae, so commit with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top