Function Problems

Count Matching Values Across Excel Worksheets with COUNTIF

Maira MehtabMaira Mehtab Nov 22, 2026 868 views

Question details

Count values matching a criterion across several monthly worksheets without writing a separate COUNTIF formula for each sheet.

Count Matching Values Across Excel Worksheets with COUNTIF
Product
Microsoft Excel 2010
Device & OS
not provided
Scenario
The same cell range on each monthly worksheet needs to be checked against a criterion in a summary sheet.
Observed behavior
A single formula uses a list of worksheet names to return the combined count of matching values.
Before you start

Confirm that each monthly worksheet uses the same range, such as B2:AF2, and identify the cell containing the value to count. Check the worksheet tabs for their exact names before entering the sheet list.

Solution 1Recommended

Use COUNTIF and INDIRECT with a worksheet name list

Use this formula when all worksheets have the same range to check and their names are listed in cells.

1
List the worksheet names

On the summary worksheet, enter one existing sheet name per cell in M4:M15, such as Jan, Feb, and Mar. Use only the cells that contain valid sheet names.

2
Enter the criterion

Type the value you want to count in cell D1 of the summary worksheet.

3
Enter the formula

Select the cell where you want the total and enter =SUMPRODUCT(COUNTIF(INDIRECT("'"&SUBSTITUTE($M$4:$M$15,"'","''")&"'!B2:AF2"),D1)). Press Enter.

4
Adjust the references

In the formula bar, change $M$4:$M$15 to match the populated sheet-name list, B2:AF2 to the range on each sheet, and D1 to the criterion cell if needed.

Use COUNTIF and INDIRECT with a worksheet name list
Check sheet names and list cells: Blank list cells or names that do not match existing worksheet tabs can produce a #REF! error. The formula includes quotes around sheet names and escapes apostrophes in them.
Count across worksheets in WPS

Count matching values across sheets in WPS Spreadsheets

Use a worksheet-name list with COUNTIF, INDIRECT, and SUMPRODUCT in WPS Spreadsheets to total matches across the same range on multiple sheets.

  1. 1. Open the workbook: In WPS Spreadsheets, choose Open and select your Excel workbook.
  2. 2. Prepare the sheet list: On a summary worksheet, enter the exact worksheet names in M4:M15, using only the cells needed, and enter the criterion in D1.
  3. 3. Enter the count formula: Select an empty cell and enter =SUMPRODUCT(COUNTIF(INDIRECT("'"&SUBSTITUTE($M$4:$M$15,"'","''")&"'!B2:AF2"),D1)). Adjust the sheet list and range as needed, then press Enter.
  4. 4. Check the result: Compare the total with the matching cells on the worksheet tabs. If you see an error, verify that every listed sheet exists and remove blank cells from the referenced list.
Count matches across monthly worksheets from one formulaOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and worksheet tabsAccess spreadsheet tools for free
microsoft office alternative - wps office

Frequently Asked Questions

What if I have fewer than 12 worksheets?

List only the sheets you need and change $M$4:$M$15 in the formula to the populated range. For example, use $M$4:$M$6 for three sheet names.

Can the worksheet names contain spaces or apostrophes?

Yes. The formula surrounds each name with single quotes and uses SUBSTITUTE to escape apostrophes. Enter each name in the list exactly as it appears on its worksheet tab.

Why does the formula return #REF!?

A listed sheet may not exist, a name may be misspelled, or the list range may include blank cells. Check the worksheet tabs and adjust the list range in the formula bar.

How do I count a different range or value?

Replace B2:AF2 in the formula with the same target range used on each sheet. Change D1 to the cell containing your desired COUNTIF criterion.