Sorting numbers, please help?

I ahve a spreadsheet that contains 5 columns of whole numbers rangeing from 1
to 100. I need to count how many times each number appears in each column or
even in all 5 columns together. Ex. the number 1 appears 252 times, etc.

Does anyone know how to sort the data that way? I have imported into an
access table as well if that's easier.

Please help?

Hopefully someone has a better method, but:

Assuming that you want to keep the results in 5 columns to match the same number of rows, I have used A1 to E9 as my range, you will need to adjust the range for the number of rows that you have.

assuming that columns G to K are clear, in G1 put
=SMALL($A$1:$E$9,ROW())&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW()))

in H1 put
=SMALL($A$1:$E$9,ROW()+9)&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW()+9))

in I1 put
=SMALL($A$1:$E$9,ROW()+18)&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW()+18))

in J1 put
=SMALL($A$1:$E$9,ROW()+27)&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW()+27))

in K1 put
=SMALL($A$1:$E$9,ROW()+36)&" = "&COUNTIF($A$1:$E$9,SMALL($A$1:$E$9,ROW()+36))

(adjust the numbers 9, 18, 27 & 36 accordingly)

Formula-drag those to the number of rows for which you have data.

(you can use just one column and drag it 5 times the number of rows)


Hope this helps

Originally Posted by Kevin Schultz

I ahve a spreadsheet that contains 5 columns of whole numbers rangeing from 1
to 100. I need to count how many times each number appears in each column or
even in all 5 columns together. Ex. the number 1 appears 252 times, etc.

Does anyone know how to sort the data that way? I have imported into an
access table as well if that's easier.

Please help?

you could use CountIf function on excel.
e.g CountIF("A1:A500", 1) which will should how many time 1 appear from
range (A1 to A500).


"Kevin Schultz" wrote:

> I ahve a spreadsheet that contains 5 columns of whole numbers rangeing from 1
> to 100. I need to count how many times each number appears in each column or
> even in all 5 columns together. Ex. the number 1 appears 252 times, etc.
>
> Does anyone know how to sort the data that way? I have imported into an
> access table as well if that's easier.
>
> Please help?