Taking names from Multiple Sheets and having a function Count the number of times the name appears on the different sheets

GDOG_27

New Member
Joined
Jun 1, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Practically I have around 10 different sheets with names on these sheets in column A. I would like to in a separate sheet have the name and the number of times the name is found in the 10 other sheets. For example, the name Tom is posted three times in sheet-1, four times in sheet-2 and one time in sheet-3. I would like in a new sheet to be able to type "Tom" into column A and then in column B next to Tom show the number of times it was found in the multiple sheets. This needs to be a function that I can put a new name into column A and then it searches for the number of times it is found in the 10 different sheets.

The function I'm using is =COUNTIF(Sheet1!A2:A,Sheet2!A2:A) this works for comparing just the 2 sheets and will show a correct number but when I try formatting for multiple sheets it will not work. here this is kind of a what the solution would be like except of course in different sheets. Also note that when adding a new name to the "master list" it should try to find the name. Sheets 1,2, and 3 will be updated automatically so manually I would write the name in the master, and it then gives me the number of times the newly added name appears.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
have you tried something like below

change Sheet8 to your sheet names

="sheet1=" & COUNTIF(Sheet8!A:A, C3) & " Sheet2=" & COUNTIF(Sheet8!B:B, C3)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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