Forum Discussion
Counting occurrences over multiple worksheets
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...
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.