I have 5 columns of numbers and at the bottom of each column I want to list every number in order from least to greatest and how many times it occurred in the column.
I'll just provide you with 5 rows of numbers so I don't waste a ton of space.
06 28 31 38 41
08 14 36 40 45
02 03 19 30 42
01 05 10 15 18
20 21 23 33 35
In A7, =SMALL(A$1:A$5, ROWS(A$7:A7))
Copy down to A11, then across.
In A13 and copy down and across, =COUNTIF(A$1:A$5, A7)
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thank you for your quick reply.
The first function worked perfectly.
The second one
doesn't seem accurate. I filled it out and dragged it down and across, but it's returning incorrect results.Code:=COUNTIF(A$1:A$5, A7)
Now that we sorted the numbers I need to figure out how to total how many of each number there are. for example. how many 01's there are, how many 02's there are, how many 03's there are, etc.
I can manually go through using
From the sorted list we created earlier but there has got to be an easier way.Code:=COUNT(A...:A...)
There are no repeated numbers in any column.Code:A- B- C- D- E- 1 6 28 31 38 41 2 8 14 36 40 45 3 2 3 19 30 42 4 1 5 10 15 18 5 20 21 23 33 35 6 7 1 3 10 15 18 8 2 5 19 30 35 9 6 14 23 33 41 10 8 21 31 38 42 11 20 28 36 40 45 12 13 1 1 1 1 1 14 1 1 1 1 1 15 1 1 1 1 1 16 1 1 1 1 1 17 1 1 1 1 1
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Misunderstanding. This is perfect. Thanks.
You're welcome. Would you please mark the thread as Solved?
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
One more question/tweak.
In the second (counting) section, instead of listing the numbers like the first section.
Can we make it Rank the numbers or something?
example:
01 20 times
02 07 times
03 43 times
etc.
edit*
Right now for the number 01 for example it lists it like
01
01
01
01
01
01
01
01
01
01
01
and then the next number like
02
02
02
02
02
02
02
02
Last edited by w3dgie; 09-25-2009 at 06:27 PM. Reason: clearer example
Change the formula in A13 to =TEXT(A7, "00 ") & TEXT(COUNTIF(A$1:A$5, A7),"00 ") & "times"
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
When i drag it down now it does the same thing but it says
00 20 times
01 20 times
01 20 times
01 20 times
01 20 times
01 20 times
01 20 times
01 20 times
01 20 times
etc
EDIT*
I had a typo, corrected the 00 20 times.
but it does continue to list 01 20 times.
and there are no 00's
Last edited by w3dgie; 09-25-2009 at 06:48 PM.
It shows the number of times that the values that appear, appear:
Code:-----A----- 1 6 2 8 3 2 4 1 5 20 6 7 1 8 2 9 6 10 8 11 20 12 13 01 01 times 14 02 01 times 15 06 01 times 16 08 01 times 17 20 01 times
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
try adding more than one of each number.
edit*
it works fine when I drag it horizontally because the numbers are being displayed for the first time but when i drag it down and the numbers from the first section (sorted numbers) are being displayed more than once.
Last edited by w3dgie; 09-25-2009 at 06:59 PM.
Code:-----A----- 1 6 2 8 3 20 4 1 5 20 6 7 1 8 6 9 8 10 20 11 20 12 13 01 01 times 14 06 01 times 15 08 01 times 16 20 02 times 17 20 02 times
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
yeah, see where it says
16 20 02 times
17 20 02 times
i just want it to say
20 02 times
one time on one line. if possible.
Change A13 to =IF(COUNTIF(A$7:A7,A7) > 1, "", TEXT(A7, "00 ") & TEXT(COUNTIF(A$1:A$5, A7), "00"" times""") )
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks