Counting occurrences over multiple worksheets

Copper Contributor

I am working with machinery. And when i do maintenance, i need to count the used items for each machine ( i noted down each item in each machine every time anything got replaced ) and add them all up in a spreadsheet for inventory check and further orders.


In this spreadsheet, i need to count that single used item in a mix of different items in multiple days from 20 machines (each machine has a spreadsheet). for example: belt 1595x5Mx25, belt 1400x8Mx20, belt 2400x8Mx25. Is there a way to count the total "belt 1595x5Mx25" across the whole 20 spreadsheets? And can it automatically update if i add more in the future? 


Thank you 



1 Reply


On the summary sheet, or on a separate sheet, make a list of the names of the 20 machine worksheets, exactly as they as spelled in the sheet tab.

Select the list, then click in the name/cell address box on the left hand side of the formula bar.

Type the name Sheets and press Enter. The list is now named Sheets.

On the summary sheet, enter the name of a specific item such as belt 1595x5Mx25 in a cell, say in A2.

In the cell next to it, B2, enter the formula

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!D2:D100"), A2))

where D2:D100 is the range listing the items used on each of the machine sheets.