Function Problems

How to Count Excel Items with a Specific Status Across Multiple Sheets

Steven Parker Oct 14, 2026 869 views

Question details

The user needs a formula to count a specific item across multiple worksheets, but only when the corresponding status is marked as 'In Use'.

How to Count Excel Items with an 'In Use' Status Across Multiple Sheets

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Calculating the total number of specific active items distributed across various worksheets within a single workbook.
Observed behavior: The goal state is to match an item name in column D and the status 'In Use' in column E across multiple sheets using a named range for the sheet names, without triggering a 'too few arguments' error.

Before you start

Ensure you have created a Named Range (e.g., 'Sheets') that lists the exact names of all the worksheets you want to include in your calculation.

Solution 1Recommended

Use SUM, COUNTIFS, and INDIRECT Functions

Combine these functions to dynamically reference multiple sheets from a named range and count items based on multiple criteria.

The COUNTIFS function natively only supports referencing a single sheet. To count across multiple sheets, you must wrap COUNTIFS inside a SUM function and use INDIRECT to evaluate an array of sheet names.

When referencing sheet names through INDIRECT, it is important to include single quotes in the text string so that sheet names containing spaces are handled correctly.

Set up your item criteria

Ensure that the item you want to count is typed into a reference cell, for example, cell C3 on your summary sheet.

Enter the formula

Select the cell where you want the final count to appear and input the following formula: =SUM(COUNTIFS(INDIRECT("'"&Sheets&"'!D:D"),C3,INDIRECT("'"&Sheets&"'!E:E"),"In Use"))

Calculate the result

Press Enter to execute the formula. The formula will search column D for the item in C3 and column E for 'In Use' across all sheets listed in your 'Sheets' named range, then sum the results.

Use SUM, COUNTIFS, and INDIRECT Functions

Formula Breakdown: Using INDIRECT twice ensures that both the criteria range (D:D) and the status range (E:E) are evaluated as valid sheet references. Without the second INDIRECT, you will receive a syntax error.

Effortlessly Manage Cross-Sheet Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions, including SUM, COUNTIFS, and INDIRECT, making it incredibly easy to track complex inventory or status data across multiple worksheets.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multi-sheet data.
2. Define your named range: Go to the Formulas tab and select Name Manager to create your 'Sheets' list.
3. Apply the cross-sheet formula: Paste the provided SUM, COUNTIFS, and INDIRECT formula into your summary cell.
4. Get instant results: Press Enter to instantly calculate the total 'In Use' count across all your worksheets.

100% compatible with Microsoft Excel formulas and formattingFast processing engine for heavy, multi-sheet calculationsIntuitive formula builder that helps you avoid syntax errorsLightweight, secure, and completely free to download

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #REF! error when using INDIRECT?

A #REF! error usually occurs if the sheet names listed in your named range do not exactly match the actual names of the worksheet tabs, or if a listed sheet has been deleted.

Can I use standard 3D references with COUNTIFS?

No, Excel and standard spreadsheet tools do not support 3D referencing (like 'Sheet1:Sheet3'!D:D) inside the COUNTIFS function. You must use the INDIRECT and SUM array method to achieve this.

How do I create a Named Range for my sheet names?

Type the names of your sheets into a column of cells. Highlight those cells, click in the Name Box (to the left of the formula bar), type 'Sheets' (without quotes), and press Enter.

Excel & Data Problems