Counting Numbers

[Funkydan]

Hello All,

Woundering if you can help me?

I've created i table with numbers in rangeing from 1 - 50
the table may have 1 repeated in it 10 times and 23 repeated 6 times
and so on

what i would like to do in another cell is have a formula that counts
how many times the Number 1 is Present and so on for each number. is
there a way i can do this?

Regard s

Danny

 

[Guest]

The easiest thing to do is to use a Pivot Table. You can list each unique
number and how many times it occurs. This technique will work with words as
well as numbers. If you are not familiar with pivot tables, they are
well-explained in:

http://peltiertech.com/Excel/Pivots/pivottables.htm

The alternative is using COUNTIF(), but this requires a separate formula for
each value.

 

[Nick Hodge]

s

Set up a table with the numbers 1-50 in, say in J1:J50. (I am making a
presumption your data table is in A1:A1000, just change the range in the
formula to suit)

In K1 enter

=COUNTIF($A$1:$A$1000,J1)

and copy down to J50

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk

 

[Funkydan]

Thank you Very Much for the Information it worked like a treat,

My data is in Cell C5 to G36 so all i did was use the formula
=COUNTIF(C5:G36,1)
And it gave me total of how many 1's there was, so i copied the formula
and had ,2 and so on,

Is the there a quick way a copying the formula for each number, cause
when i use the copy drop down, it changed the Formula to
=COUNTIF(C6:G37,1) THEN THE NEXT ONE IS -COUNTIF(C7:G38,1) ?