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. For
example, the number of 10's is 4, the number of 20's is 3.

Antone got any ideas?


Thanks

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=513150

=COUNTIF(A:A,10)

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cj21" wrote in message
...

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. For
example, the number of 10's is 4, the number of 20's is 3.

Antone got any ideas?


Thanks

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=513150

see he

http://www.cpearson.com/excel/excelF.htm#CountBetween

will be an array formula, so for example,

=SUM((A1:A100)*(A1:A10<10))

you need to hold in shift, ctrl and hit enter once the formula is input


--
rocket0612
------------------------------------------------------------------------
rocket0612's Profile: http://www.excelforum.com/member.php...o&userid=19492
View this thread: http://www.excelforum.com/showthread...hreadid=513150

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)


--
rocket0612
------------------------------------------------------------------------
rocket0612's Profile: http://www.excelforum.com/member.php...o&userid=19492
View this thread: http://www.excelforum.com/showthread...hreadid=513150

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

Thanks
Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=513150

=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)

"cj21" wrote in message
...

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

Thanks
Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile:
http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=513150

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


"cj21" wrote:


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. For
example, the number of 10's is 4, the number of 20's is 3.

Antone got any ideas?


Thanks

Chris


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=513150