counting numbers

C

cj21

I have a list of numbers. e.g

10
10
5
0
5
10
20
25
20
15
60
60
10
20


I want a formula that tells me how many there are of each value. Fo
example, the number of 10's is 4, the number of 20's is 3.

Antone got any ideas?


Thanks

Chri
 
R

rocket0612

you can just use a countif for this:

=COUNTIF(A1:A10, 1)

would count in the range a1:a10 the number of cells containing the
number 1

or if you need to count lots of numbers, put the number in B1 you want
to count and then you can change it to the new number after:

=COUNTIF(A1:A10, B1)
 
B

Bob Phillips

=COUNTIF(A:A,10)

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
C

cj21

Is it possible to amend this formula? Suppose i want it to count the
amount of numbers between 0 and 10.

Thanks
Chris
 
B

Bob Phillips

=sumproduct(--(A1:A1000>=0),--(a1:a1000<=10))

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Michael

Hi cj21. If you sort the numbers in column A ascending and then go to Data -
Subtotals and specify count, it will tell you the number of each number. Put
labels in row 1 for all your columns.

Sincerely, Michael Colvin
 
Top