Counting number of Times "name" appears across multiple sheets

Aridhol

New Member
Joined
Jan 18, 2009
Messages
2
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:

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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.
 
Upvote 0
Aridhol,

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

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




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)+COUNTIF(Sheet6!A:A,A1)


Have a great day,
Stan
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top