Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Counting number of Times "name" appears across multiple sheets

  1. #1
    New Member
    Join Date
    Jan 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Counting number of Times "name" appears across multiple sheets

    Hello, I'm trying to count the number of times a name appears across multiple worksheets but I can't seem to figure it out.


    I have 6 sheets to count and all the names are in column A (from A1:A100) on each sheet. The names are not in a particular order.

    On sheet 7 I want to have a cell beside each persons name that counts the number of times their name appeared on the other 6 sheets.

    For example Sheet 7 (called total) would have

    Joe 5 (where 5 is the outcome of the formula I'm looking for to count all the times "Joe" appears on sheets 1-6).

    I have it counting per sheet with =COUNTIF(A1:A130,"name") but this is not quite what I am looking for.

    Thanks in advance for any advice.


    Edit: I am using Excel 2007 w/ windows XP
    Last edited by Aridhol; Jan 18th, 2009 at 06:36 PM.

  2. #2
    Board Regular
    Join Date
    May 2008
    Posts
    836
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting number of Times "name" appears across multiple sheets

    Hello and Welcome to the Board!
    On Sheet7 if A1 is the name of the person, use this formula in B1
    =COUNTIF(Sheet1!A1:A130,A1) + COUNTIF(Sheet2!A1:A130,A1) + COUNTIF(Sheet3!A1:A130,A1) + COUNTIF(Sheet4!A1:A130,A1) + COUNTIF(Sheet5!A1:A130,A1) + COUNTIF(Sheet6!A1:A130,A1)
    You may want to try COUNTIFS function of excel 2007.
    Hope this helped.

  3. #3
    Banned
    Join Date
    Jul 2006
    Location
    Northeast Pennsylvania
    Posts
    3,656
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting number of Times "name" appears across multiple sheets

    Aridhol,

    Create a unique list of names on sheet "Totals":

    Totals

     AB
    1Joe14
    2Peter14
    3Stan14
    4Jacob14
    5Jason13
    6Steven13
    7Kathleen12
    8Marita12

    Spreadsheet Formulas
    CellFormula
    B1=COUNTIF(Sheet1!A:A,A1)+COUNTIF(Sheet2!A:A,A1)+COUNTIF(Sheet3!A:A,A1)+COUNTIF(Sheet4!A:A,A1)+COUNTIF(Sheet5!A:A,A1)+COUN TIF(Sheet6!A:A,A1)


    Excel tables to the web >> Excel Jeanie HTML 4



    Then copy the formula in cell B1 (down to the end of the list of unique names in column A):
    =COUNTIF(Sheet1!A:A,A1)+COUNTIF(Sheet2!A:A,A1)+COUNTIF(Sheet3!A:A,A1)+COUNTIF(Sheet4!A:A,A1)+COUNTIF(Sheet5!A:A,A1)+COUN TIF(Sheet6!A:A,A1)


    Have a great day,
    Stan
    Last edited by stanleydgromjr; Jan 18th, 2009 at 07:14 PM.

  4. #4
    New Member
    Join Date
    Jan 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting number of Times "name" appears across multiple sheets

    Thank both of you! It seems to work just fine!

    I used C_M's solution as I saw it first. I really appreciate the help.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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