How to Count Items Across Multiple Excel Worksheets Automatically
Question details
The user wants to track and count the occurrences of a specific item (such as a replacement part) across multiple worksheets and have the total count update automatically.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking inventory or parts spread across different machine or departmental worksheets without manually opening and checking each sheet.
- Observed behavior
- Needing a dynamic formula approach to aggregate item counts across a large number of sheets (e.g., 20 machine sheets) into a single summary sheet.
Ensure all your target worksheets have a consistent structure, meaning the items you want to count should be located in the exact same column or range across every sheet.
Use SUMPRODUCT and INDIRECT to Count Across Sheets
Combine the SUMPRODUCT, COUNTIF, and INDIRECT functions to dynamically calculate the total occurrences of an item from a defined list of worksheet names.
Since the COUNTIF function does not support 3D referencing (e.g., Sheet1:Sheet20), you can bypass this limitation by creating a list of your sheet names and using INDIRECT to reference them dynamically.
On a summary sheet, create a vertical list containing the names of all the target machine worksheets exactly as they appear on the sheet tabs.
Select the list of sheet names, click the Name Box (located to the left of the formula bar), type 'Sheets', and press Enter. This creates a named range called Sheets.
Type the specific item name you want to count (such as 'belt 1595x5Mx25') into cell A2 of your summary sheet.
In cell B2, enter the formula: =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!D2:D100"),A2)). Make sure to replace D2:D100 with the actual range where the items are stored on each individual machine sheet.
Consolidate Data Using Power Query
For better performance on very large workbooks and easier maintenance, combine the data from all sheets into a single consolidated table using Power Query instead of volatile INDIRECT functions.
Count Across Multiple Sheets Seamlessly in WPS Office
WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT and INDIRECT, allowing you to easily manage complex multi-sheet calculations just like in Excel. It provides a familiar, fast, and completely free environment to handle heavy data tasks.
- 1. Open your Workbook: Launch WPS Spreadsheet and open the workbook containing your multi-sheet data.
- 2. Define the Named Range: List your target sheet names, select them, and use the Name Box to define them as 'Sheets'.
- 3. Insert the Formula: Use the standard =SUMPRODUCT(COUNTIF(INDIRECT...)) formula to instantly aggregate your item counts across the sheets.
Frequently Asked Questions
Why is my INDIRECT formula returning a #REF! error?
A #REF! error usually occurs if the sheet names in your named range list contain typos, have extra spaces, or do not exactly match the actual sheet tabs. It can also happen if one of the referenced sheets was deleted or renamed.
Can I use 3D references like COUNTIF(Sheet1:Sheet20!D2:D100, A2)?
No, the COUNTIF and COUNTIFS functions do not support 3D references across multiple sheets. You must use the SUMPRODUCT and INDIRECT combination or consolidate your data into a single sheet first.
Will the INDIRECT formula update if I add a new worksheet?
Not automatically. If you add a new worksheet, you must manually add the new sheet's name to your summary list and ensure your 'Sheets' named range expands to include the new cell. Once updated, the formula will include the new sheet in its count.
Does this formula work if my sheet names contain spaces?
Yes, it works perfectly because the formula includes single quotes around the sheet name reference ("'"&Sheets&"'"). This ensures that any sheet name containing spaces or special characters is parsed correctly by the INDIRECT function.
