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