Function Problems

How to Count Matching Values Across Multiple Excel Sheets Using Drop-Down Criteria

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to count specific values, such as court names or levels, across multiple worksheets based on dynamic criteria selected from a drop-down list.

Product: Excel
Device & OS: not provided
Scenario: Consolidating and counting data occurrences across a 3D range of worksheets using a dynamic drop-down selection for the lookup criteria.
Observed behavior: The user is looking for the correct lookup or counting formula combination, and community experts are requesting a sanitized version of the workbook to troubleshoot the specific data structure.

Before you start

Ensure all the worksheets you want to count across share identical layouts, and create a master list of all your target sheet names in a single column to use in your formula.

Solution 1Recommended

Use SUMPRODUCT, COUNTIF, and INDIRECT Functions

This is the standard and most reliable method to count values across multiple sheets using dynamic criteria without macros.

Because the standard COUNTIF function does not natively support 3D references (e.g., Sheet1:Sheet5!A1:A10), you must combine it with SUMPRODUCT and INDIRECT. INDIRECT translates a text list of your sheet names into actual references, COUNTIF counts the criteria on each sheet, and SUMPRODUCT adds those counts together.

List Your Sheet Names

In an empty column, type the exact names of all the sheets you want to include in the count. Select these cells, click the Name Box (next to the formula bar), type 'SheetList', and press Enter to create a named range.

Create the Drop-Down Criteria

Select the cell where you want your drop-down list (e.g., B1). Go to Data > Data Validation, choose 'List' under Allow, and input your source criteria (e.g., court names). Click OK.

Enter the Multi-Sheet Count Formula

Select your result cell and enter the formula: =SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!A2:A100"), B1)). Replace 'A2:A100' with the range containing the values on your sheets, and 'B1' with your drop-down cell reference.

Syntax Matters: Make sure to include the single quotes "'" in the INDIRECT function as shown. This ensures the formula won't break if any of your worksheet names contain spaces.

Solution 2

Share a Sanitized Workbook for Community Troubleshooting

If your data structure is complex or the formula returns an error, sharing a simplified, secure version of your file helps support teams identify the issue.

Advanced Data Analysis

Count Across Multiple Sheets Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like SUMPRODUCT, COUNTIF, and INDIRECT, allowing you to seamlessly count matching values across multiple tabs. It provides an intuitive interface for data validation and complex multi-sheet consolidations.

1. Set Up Data Validation: Open your workbook in WPS Spreadsheet. Go to the Data tab and click Data Validation to easily create your drop-down list.
2. Define Your Sheet Names: Select the cells containing your sheet names, right-click, and choose 'Define Name' to quickly create a named range.
3. Apply the Array Formula: Input your =SUMPRODUCT(COUNTIF(INDIRECT(...))) formula. WPS Spreadsheet will calculate and update the results instantly as you change your drop-down selection.

Highly compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Flawlessly executes complex array formulas for multi-sheet calculations.Free and lightweight alternative equipped with professional data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDIRECT formula returning a #REF! error when counting across sheets?

This usually happens for two reasons: either a sheet name in your list contains spaces but isn't wrapped in single quotes within the formula syntax, or one of the sheets listed in your 'SheetList' range does not exist in the workbook or is misspelled.

Can I use 3D references like Sheet1:Sheet3 with COUNTIF?

No, standard Excel and WPS Spreadsheet COUNTIF functions do not support 3D references natively. You must use a combination of SUMPRODUCT, COUNTIF, and INDIRECT referencing a list of sheet names.

How do I protect sensitive information before sharing an Excel file for support?

Always create a duplicate copy of your file first. Delete or manually overwrite sensitive data (like real names, addresses, and financials) with generic text (e.g., 'Court A', 'Name B'). Ensure the fundamental layout and problem mechanics remain intact before uploading to a cloud drive.

Can I use wildcards with this multi-sheet counting method?

Yes, you can include standard wildcards like asterisks (*) for multiple characters or question marks (?) for single characters in your drop-down criteria. The COUNTIF function within the SUMPRODUCT array will evaluate them correctly across the referenced sheets.

Excel & Data Problems