Solved

Excel 2010:  Count

Posted on 2013-06-20
4
428 Views
Last Modified: 2013-06-20
Hi Experts!  I need a formula that will count how many times a name appears in column A.  For example after I sort that column from A to Z i need to get a count of hown many times that username appears.   I could do this manually but that column is about 2000 rows, it would take forever.

JDOE
JDOE
JDOE
         3 imes

Thank you.
0
Comment
Question by:itsmevic
4 Comments
 
LVL 23

Assisted Solution

by:NBVC
NBVC earned 175 total points
ID: 39263696
Do you want a count in an adjacent column using a formula like:

=COUNTIF(A:A,A1) copied down

If you want a count below each name after sorting, you can use SUBTOTALS from the Data Menu.. select Count at the Use Function field.. ensure correct column is selected at the top and check same column in the bottom section...

If you want a table of all names and counts separately, you can use a Pivot Table... Insert|Pivot table, then drag the name to the Row area, and the Name again to the Summation area... it should give count of each name.
0
 
LVL 25

Assisted Solution

by:Ron Malmstead
Ron Malmstead earned 165 total points
ID: 39263698
=COUNTIF(A1:A2000,"JDOE")
0
 

Assisted Solution

by:mrbcam21
mrbcam21 earned 110 total points
ID: 39263725
=COUNTIF(A:A,"JDOE")
0
 
LVL 24

Accepted Solution

by:
Steve earned 50 total points
ID: 39263847
****  just read NB_VC after posting this one  ****
**** I agree with him that you should use pivot table  ****

Essentially same as NB_VC... (so no point for me on this one)
Add a column header (if not there already) such as UserName
Highlight data in A:A including header and go to Data > Pivot table
Click OK
Drag the UserName Field into the Row area and the Sum (sigma symbol) area.

You should now have the list of usernames in order and with a count next to each.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Move data from one sheet to another 11 36
Excel fill in a form with pick list or drop downs, also need calendar pick 25 23
Excel VBA 4 27
Excel Drop Down List 13 35
My experience with Windows 10 over a one year period and suggestions for smooth operation
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question