Excel SUBTOTAL With COUNTIF: Count Visible Matches

Formulas & functions · Updated November 22, 2026

To count rows that match a condition and remain visible after filtering, combine SUBTOTAL with SUMPRODUCT; COUNTIF by itself counts filtered-out rows too. With IDs in A2:A11, regions in B2:B11, and the region to count in E2, use this formula: =SUMPRODUCT(SUBTOTAL(103,OFFSET($A$2,ROW($A$2:$A$11)-ROW($A$2),0,1)),--($B$2:$B$11=$E$2)).

Why combine SUBTOTAL and COUNTIF?

COUNTIF is designed to count cells that satisfy a condition, such as a region being East. It does not have an option to ignore rows hidden by a worksheet filter. As a result, an ordinary count can still include matching records that are not currently displayed. SUBTOTAL does the opposite kind of job: it can calculate over visible rows, but it does not accept a separate condition such as region equals East.

The combined formula solves both parts of the problem. It checks each row to see whether it is visible, checks whether its region matches the requested region, and adds one for each row where both checks are true. The result changes when you apply or change an AutoFilter. This is useful for filtered reports, lists, and summaries where the count should describe only the records on screen.

The function name can be misleading: there is no special SUBTOTALIF or SUBTOTALCOUNTIF function in Excel. The solution is a formula pattern that combines existing functions, or a helper column that marks visible rows.

How the formula works

In the example layout, column A contains a nonblank ID for every record, column B contains the region, and E2 contains the region to count. The formula uses the ID column to test visibility. That choice matters: if the tested cell is blank, COUNTA cannot use it to indicate that a visible record exists.

  • ROW($A$2:$A$11)-ROW($A$2) produces a sequence of row offsets, from zero through nine.
  • OFFSET uses those offsets to refer to each ID cell in turn.
  • SUBTOTAL(103,...) returns 1 for a visible, nonblank ID and 0 for a filtered-out or manually hidden row.
  • --($B$2:$B$11=$E$2) turns each region comparison into 1 for a match or 0 for a nonmatch.
  • SUMPRODUCT multiplies the visibility and match results row by row, then adds them.

A row contributes to the final count only if both tests return 1. A visible row with another region contributes zero, as does a matching region on a hidden row. The references use dollar signs so the ranges stay fixed if the formula is copied to another cell. Change E2 to count a different region without editing the formula.

Worked example with a filter

Suppose rows 2 through 11 contain these records: 1001 East Active; 1002 West Active; 1003 East Inactive; 1004 East Active; 1005 North Active; 1006 West Inactive; 1007 East Active; 1008 North Active; 1009 East Inactive; and 1010 West Active. The IDs are in A, regions are in B, and statuses are in C. Enter East in E2, then filter column C to show Active records.

The visible East records are 1001, 1004, and 1007, so the formula returns 3. The other East records, 1003 and 1009, are hidden by the status filter and do not count. If you clear the filter, all five East records become visible and the result changes to 5. The formula counts qualifying rows, not distinct region names or unique IDs.

For this example the formula still uses A2:A11 as its visibility test, even though the filter is on column C. Filtering any column in the same list hides entire rows, so the row-level visibility test applies. Make sure the filter range includes all the related columns; filtering only part of a dataset can separate a record’s fields and produce confusing results.

Choosing 3 or 103 in SUBTOTAL

The number 103 in the formula tells SUBTOTAL to perform the COUNTA operation while ignoring manually hidden rows as well as rows removed by a filter. The closely related option is 3, which performs COUNTA and ignores filtered-out rows but includes rows that were hidden manually.

  • Use 103 when hidden rows should not count, whether they were hidden with a filter or hidden manually.
  • Use 3 when you want a manually hidden row to count, but still want AutoFilter-hidden rows excluded.

For the direct formula, replacing 103 with 3 changes how manually hidden records are treated. It does not change the region test. These function numbers apply to the COUNTA operation; the 3 and 103 choices are not COUNTIF variants. If every row has an ID, COUNTA is a practical visibility marker. If your chosen marker column can contain blanks, use another column that is reliably populated.

A helper-column alternative

A helper column is often easier to inspect and maintain than the single formula, especially for a large or frequently edited list. Assume IDs are in A2:A11, regions in B2:B11, and the target region is in E2. In C2, enter this formula and fill it down through C11: =SUBTOTAL(103,A2). It displays 1 for a visible row with an ID and 0 for a hidden row.

With those same ranges, the summary formula is =COUNTIFS(C2:C11,1,B2:B11,E2). It counts rows where the helper value is 1 and the region matches E2. For the example filtered to Active records, it returns 3 for East. The helper values recalculate as the filter changes, while COUNTIFS applies the two conditions to those values.

The trade-off is extra worksheet structure: users must preserve the helper formulas and avoid overwriting them. In return, it is simpler to troubleshoot. You can inspect column C to see which rows Excel considers visible. For an Excel Table, you can put the helper formula in a table column so the formula is carried into new table rows; confirm that the table’s filter covers the data you intend to count.

When to use COUNTIF, COUNTIFS, or a pivot table

Use ordinary COUNTIF when the count should include all matching records, regardless of whether rows are hidden by a filter. For example, with regions in B2:B11 and the target region in E2, =COUNTIF(B2:B11,E2) counts every East entry in the range. In the sample data it returns 5 even while the list is filtered to Active records. That difference is expected, not an error.

Use COUNTIFS when you need more than one condition, such as region and status, but remember that it also does not automatically exclude filtered rows. The helper-column approach pairs it with a visibility flag. For recurring summaries by several categories, a PivotTable may be more convenient: it can summarize a filtered dataset and group counts without maintaining a long formula. Choose based on whether you need a cell formula, a transparent row-by-row check, or an interactive summary.

Common problems and performance

  • The result includes hidden matches: Check that you are using the combined formula or the helper-column method. A plain COUNTIF or COUNTIFS does not respond to AutoFilter visibility.
  • The result is too small: Check that the marker range contains a nonblank value for every record. A blank ID yields no COUNTA visibility mark, even when its row is visible.
  • The formula returns zero: Verify that E2 matches the region text, including spaces or spelling differences, and that the ranges start and end on the same rows.
  • Rows are hidden manually: Decide whether these should count. The 103 version excludes them; 3 excludes filter-hidden rows but counts manually hidden ones.

The direct formula uses OFFSET, which is volatile: Excel recalculates volatile functions more often than many ordinary formulas. In a small or moderate list this is typically convenient, but many copies over very large ranges can add calculation work. Keep ranges limited to the actual data instead of using entire columns inside this pattern. If calculation speed or maintainability matters more than having one cell formula, consider the helper column or a PivotTable.

Frequently Asked Questions

Can I use COUNTIF with SUBTOTAL directly?
Not as a direct argument that makes COUNTIF visibility-aware. Use the SUMPRODUCT and SUBTOTAL formula to test visible rows, or add a SUBTOTAL helper column and count its visible markers with COUNTIFS.

Does COUNTIF ignore filtered rows?
No. COUNTIF counts matching cells in its specified range even when their rows are hidden by an AutoFilter. Use the combined formula if the result must change with filtering.

Will the formula update when I change the filter?
Yes. SUBTOTAL responds to rows hidden by filtering, and the formula recalculates to reflect the visible matches. If calculation is set to Manual, Excel may not refresh results until you recalculate the workbook.

Can I count visible matches in a different column?
Yes. Keep the visibility test pointed at a column that is filled in for every record, and change the criteria range to the column containing the values you want to test. The ranges must cover the same rows.

Can this formula count visible cells that match a partial word?
Yes, but the condition needs a wildcard criterion rather than an exact equality check. For a straightforward partial-text count, the helper-column method can use a wildcard criterion with COUNTIFS; check that the wildcard pattern matches your intended text and does not unintentionally include other labels.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.