Sum the amount of times a number appears in a column in Excel?
 

I am using Excel 2003. I have a spreadsheet of Soccer orders. One column
has player numbers entered. I would like to sum the number of times each
number appears in order to know how many numbers I have to order to put on
the jerseys. For instance, if I have three players, numbers 3, 12, 13. I
would have a total of : two number 1's, one number 2, and two number 3's.

Sum the amount of times a number appears in a column in Excel?
 

With data in A2 down, eg: 1,2,1,2,3,1,1,etc
In B2, copied down: =COUNTIF(A:A,A2)

A good, fast alternative is to create a pivot table. Assume col header in A1
is "Player#". Select col A, click DataPivot Table. Click NextNext. In
step3, click Layout, then drag n drop "Player#" in both the ROW and DATA
area. In the DATA area, the field should read: Count of Player#. If it
doesn't, just double click on it, change it to summarize by: Count. Click to
OK out Finish. Hop over to the pivot in the new sheet to the left for the
results. The pivot will produce a unique listing of the Player#'s in the ROW
area, and their corresponding counts in the DATA area, exactly the actual
results that I believe you seek.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik
---
"SEMonroe" wrote:
I am using Excel 2003. I have a spreadsheet of Soccer orders. One column
has player numbers entered. I would like to sum the number of times each
number appears in order to know how many numbers I have to order to put on
the jerseys. For instance, if I have three players, numbers 3, 12, 13. I
would have a total of : two number 1's, one number 2, and two number 3's.

Sum the amount of times a number appears in a column in Excel?
 

SEMonroe wrote:
I am using Excel 2003. I have a spreadsheet of Soccer orders. One column
has player numbers entered. I would like to sum the number of times each
number appears in order to know how many numbers I have to order to put on
the jerseys. For instance, if I have three players, numbers 3, 12, 13. I
would have a total of : two number 1's, one number 2, and two number 3's.

With your player numbers in A1:A50 and the number 0 through 9 in B1:B10, array
enter the following in C1:

=SUM(--((($A$1:$A$50-B1)/10)=(INT(($A$1:$A$50-B1)/10))))+SUM(--(($A$1:$A$50)<((B1+1)*10)))-SUM(--(($A$1:$A$50)<(B1*10)))

When array entered (ctrl-shift-enter), Excel will add the {braces}.

Copy the formula down to C10. This will count a leading 0 for all player
numbers from 0 to 9 (00, 01, 02, etc.) and is only good for player numbers from
0 to 99.