Thanks:  0
Likes:  0

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

1. ## 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.

Edit: I am using Excel 2007 w/ windows XP

2. ## 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. ## Re: Counting number of Times "name" appears across multiple sheets

Aridhol,

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

Totals

 A B 1 Joe 14 2 Peter 14 3 Stan 14 4 Jacob 14 5 Jason 13 6 Steven 13 7 Kathleen 12 8 Marita 12

 Cell Formula 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

4. ## 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.

## User Tag List

#### Posting Permissions

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