Go Premium for a chance to win a PS4. Enter to Win

x
Solved

Excel 2010:  Count

Posted on 2013-06-20
Medium Priority
453 Views
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
Question by:itsmevic

LVL 23

Assisted Solution

NBVC earned 700 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

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

Assisted Solution

mrbcam21 earned 440 total points
ID: 39263725
=COUNTIF(A:A,"JDOE")
0

LVL 24

Accepted Solution

Steve earned 200 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)
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

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses
Course of the Month8 days, 21 hours left to enroll

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

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