I have the multiple sheets in a single workbook.

The first sheet is having summary details:

Other sheets are like details as show below:

I want to get total count name occurred in all the sheets in workbook.

Expected output should be:

CodePudding user response：

Create a list of all relevant worksheet names and store it within Name Manager, as SheetList, say.

Your formula is then:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!A:A"),B2))

where I've assumed that the column to be queried in each of those worksheets is column A and that the first entry in the Summary sheet for which you wish to obtain a count is in cell B2.

Copy down to give results for further entries in B3, B4, etc.

CodePudding user response：

Depending on your sheetnames, try something like:

=SUM(--(TOCOL(Sheet2:Sheet3!A:A,1)=B2))