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.

I have done something similar but think I must be formatting wrong for multiple 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. enter image description 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.

CodePudding user response：

You could try the following in your master sheet to get a list of all names present in all sheets against their count:

=query({Sheet1!A2:A;Sheet2!A2:A;...;SheetN!A2:A},"select Col1,count(Col1) where Col1 is not null group by Col1",0)

N.B. we are using an array literal (curly brackets & semicolons) to union the vertical ranges from each source sheet into one long vertical range to then feed into the QUERY function. If any of the source sheets changes then the master sheet will automatically update.