+ Reply to Thread
Results 1 to 4 of 4

Counting Names In Multiple Columns

  1. #1
    Registered User
    Join Date
    11-01-2007
    Posts
    2

    Counting Names In Multiple Columns

    Hello,
    I've been using these forums for most of my questions,
    however I've not seen this one posted before so..

    I'm currently working on a client list where each
    client has a dedicated row with all of their information.

    In column A I have the clients Last name,
    In column B I have the clients First name.
    In column U I have the name of the person
    who referred the client to me: (First name Last name)

    What I need is to have column V show me how
    many clients this client has referred to me by counting
    how many times their name shows up in column U.

    I'd like it to be able to update and be linked to the column
    rather than having to enter a new formula every time I enter
    a new client.

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    If every entry in U as also listed in A,B then the formula
    Put this in somewhere in row 1 and fill down

    =COUNTIF(U:U,$B1 & " " & $A1)

    It should return the number of times that the person has refered another. You may need to tweak the space in the formula to match your formatting of col U.

  3. #3
    Registered User
    Join Date
    11-01-2007
    Posts
    2
    Thank you for the quick reply,
    how would I implement this into
    the whole column for column V.

    Also what space would I need to tweak?
    The format for column U is First name
    Last name (John Doe)

    I apologize for my newbie approach and
    questioning.

    Also I'm using Excel 2007 if that helps.

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    If you look at the help system under the function COUNTIF, it should explain things, the transfer to column V should ovbious. (If not PM me.)

    The space:
    My answer assumes that if a person is listed in A/B as "Jones" "Mary", then the U style would be "Mary Jones". If there is a different correspondence, my formula will fail. It will have problems with "Smith", "John J.", unless the U entry is "John J. Smith" (it will not count "John Smith" or "John J Smith"). If there is a consistant pattern from A/B to U other than adding the space, that term of the COUNTIF can be modified to reflect that pattern.
    Last edited by mikerickson; 11-01-2007 at 08:46 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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