+ Reply to Thread
Results 1 to 5 of 5

Count number of times names appear in a column

  1. #1
    Registered User
    Join Date
    01-06-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Count number of times names appear in a column

    I have a list of names all in one column, separated by row...

    Example...

    John Doe
    John Doe
    Jane Doe
    Jane Doe
    Jane Doe
    James Jones
    James Jones

    I want to count how many times each name appears. Like this:

    John Doe | 2
    Jane Doe | 3
    James Jones | 2

    This is a very large list of names and I prefer not to have to
    type each single name into a formula because there are hundreds
    of separate names.

    Thanks in advance for the help.
    Last edited by jeremysayshi; 01-06-2014 at 01:25 PM. Reason: solved

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Count number of times names appear in a column

    assuming your names are in column A then in column B use below formula

    =countif($A$1:$A$1000,$A$1:$A$1000) and drag down
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: Count number of times names appear in a column

    welcome to the forum, jeremysayshi. the easiest way is to do a Pivot Table. select the data, go to Insert -> PivotTable -> OK. put names in Row Labels & Names again in Values. if you don't want the Grand Total, right-click on it & Remove Grand Total.

    if you want formulas, then try this array formula to get unique names in say G2 onwards:
    =IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF(G$1:G1,$A$2:$A$10),0))&"","")

    copy down as many cells as you need. then in H2, use a simple COUNTIF:
    =COUNTIF(A:A,G2)
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Count number of times names appear in a column

    other way is to select all the name, copy and paste on a separate sheet go to remove duplicate by this way you will have all unique name then in column next to name. type formula as =countif(sheet1!$A$3:$A$1000,sheet2!A1) and drag down

  5. #5
    Registered User
    Join Date
    01-06-2014
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Count number of times names appear in a column

    Quote Originally Posted by benishiryo View Post
    welcome to the forum, jeremysayshi. the easiest way is to do a Pivot Table. select the data, go to Insert -> PivotTable -> OK. put names in Row Labels & Names again in Values. if you don't want the Grand Total, right-click on it & Remove Grand Total.

    if you want formulas, then try this array formula to get unique names in say G2 onwards:
    =IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF(G$1:G1,$A$2:$A$10),0))&"","")

    copy down as many cells as you need. then in H2, use a simple COUNTIF:
    =COUNTIF(A:A,G2)
    PERFECT! Thank you all for your help. It's truly appreciated.
    Forums like this need to have a "tip" button. :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count unique names only, not the number of times it appears
    By amyp22x3 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-04-2013, 01:32 PM
  2. [SOLVED] Count unique names only, not the number of times it appears
    By amyp22x3 in forum Excel General
    Replies: 7
    Last Post: 04-04-2013, 11:37 AM
  3. [SOLVED] count the number of times each string appears in a column and make a summary column
    By intothewild in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-04-2012, 08:02 AM
  4. [SOLVED] Count number of times of name appears in a column
    By Ness78 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 08-01-2012, 08:14 AM
  5. Counting number of times names show up in column?
    By danny2000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-28-2007, 09:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1