Function Problems

Excel Formula to Count Matching Values in a Specific Cell Across Multiple Sheets

Adam Davis Oct 22, 2026 869 views

Question details

The user needs to count how many worksheets within a workbook contain a specific target value in a designated cell, such as cell C16.

How to Count Matching Values in a Specific Cell Across Multiple Excel Sheets

Product: Excel
Device & OS: not provided
Scenario: Summarizing data across an entire workbook by checking a fixed cell reference on multiple sheets to see if it matches a specific condition.
Observed behavior: The user wants to output the total count of sheets that match the given criteria in a specific cell without manually checking each individual sheet.

Before you start

Create a complete list of all your worksheet names in a single column and define this range as a named range called 'Sheets' before applying the formula. Double-check that there are no trailing spaces in the sheet names.

Solution 1Recommended

Use SUMPRODUCT, COUNTIF, and INDIRECT Functions

Combining these functions allows you to dynamically reference multiple sheet names and count how many times your target value appears in a specific cell across those sheets.

The INDIRECT function dynamically references the sheet names listed in your named range. COUNTIF evaluates the condition in the specified cell for each sheet, and SUMPRODUCT calculates the total count of matches across the generated array.

Create a named range for sheet names

In your summary sheet, type all the exact names of the worksheets you want to include in the count into a single column. Highlight these cells, click on the Name Box (located next to the formula bar), type 'Sheets', and press Enter.

Enter the summary criteria

In an empty cell, for example C1, type the value you want to look for across the worksheets.

Apply the combination formula

Select the cell where you want the final count to appear and enter the formula: =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!C16"),C1)). Press Enter to execute it.

Drag to copy formula for other values

If you have other criteria values in cells C2, C3, or C4, click the bottom right corner of your formula cell and drag it down. The named range 'Sheets' and the target cell reference '!C16' will remain constant while checking the new criteria.

Use SUMPRODUCT, COUNTIF, and INDIRECT Functions

Exact Match Required: Ensure every name in the 'Sheets' named range exactly matches the actual worksheet tabs, including spaces. Any discrepancy or typo will result in a #REF! error.

Powerful Spreadsheet Data Analysis

Effortlessly Calculate Across Multiple Sheets in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like SUMPRODUCT, COUNTIF, and INDIRECT, making it incredibly easy to summarize data across multiple worksheets.

1. Open your workbook in WPS: Launch WPS Office and open your spreadsheet file containing the multiple worksheets.
2. List your sheet names: Type your sheet names into a single column and define the named range as 'Sheets' using the Name Box.
3. Input the combination formula: Type =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!C16"),C1)) into your target result cell and press Enter.
4. Analyze the results: Instantly view the total count of matching sheets and drag the fill handle to apply the formula to other criteria cells.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Advanced calculation engine handles complex array formulas and cross-sheet references smoothly.Cross-sheet referencing is intuitive, fast, and error-free.Lightweight software with a familiar, easy-to-use interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #REF! error?

A #REF! error usually occurs if the sheet names listed in your 'Sheets' range do not exactly match the actual tab names. Check for trailing spaces, missing punctuation, or spelling mistakes in both the listed cells and the worksheet tabs.

Can I count values in a range instead of a single cell across multiple sheets?

Yes. You can replace the single cell reference (like C16) with a range reference (like C16:C20) in the formula. For example: =SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!C16:C20"),C1)).

Does this formula update automatically if I add a new sheet?

The formula will only include the new sheet if you manually add the new sheet's name to your listed column and ensure the named range 'Sheets' is expanded to encompass that new cell.

Can I use standard 3D references like Sheet1:Sheet5!C16 with COUNTIF?

No, the standard COUNTIF function does not natively support 3D references across multiple sheets. This limitation is exactly why you must combine SUMPRODUCT, COUNTIF, and INDIRECT alongside a named list of your sheets.

Excel & Data Problems