Excel has no built-in function that combines SUBTOTAL and COUNTIF: SUBTOTAL can ignore filtered-out rows, but it cannot apply COUNTIF’s condition. To count matching visible rows, use a helper column with SUBTOTAL and then SUMIFS, or use a single SUMPRODUCT formula with SUBTOTAL and OFFSET.
Why SUBTOTAL and COUNTIF behave differently
COUNTIF counts cells that meet a condition, such as a region name or status. It does not distinguish visible rows from rows hidden by a filter. For example, filtering a list to show only open orders does not make COUNTIF stop counting matching orders in the filtered-out rows.
SUBTOTAL works in the opposite way: it performs operations such as counting or summing while excluding rows hidden by a filter, but it has no criteria argument. Its first argument selects the operation, and its remaining arguments are the ranges to calculate. That is why a formula such as SUBTOTAL with a COUNTIF-style condition cannot be written by simply combining the two function names.
This difference matters whenever a filtered list is meant to answer a question about the rows currently on screen. A normal COUNTIF can still be useful for totals across the entire dataset, but it is not a visible-rows-only count.
What SUBTOTAL counts
For a basic count of nonblank cells, SUBTOTAL uses function number 2 for COUNT and 3 for COUNTA. The function numbers 102 and 103 perform the corresponding counts while also excluding manually hidden rows. All four choices exclude rows removed by a filter.
COUNT counts numeric values; COUNTA counts nonblank cells, including text. If the goal is to count visible records, COUNTA is usually the more relevant operation, provided the chosen record column is filled for every record. A blank in that column will not be counted.
For example, with record IDs in C2:C8, the formula =SUBTOTAL(103,C2:C8) counts visible, nonblank IDs and also excludes manually hidden rows. Change 103 to 3 if manually hidden rows should still be included. Neither formula counts only rows that match a particular region or status.
Recommended method: use a helper column
A helper column is the easiest approach to inspect and maintain. It creates a visible-row flag for each record, then sums those flags only where the criteria match. In this example, column A contains regions, column B contains order status, and column C contains a nonblank ID for every record. Column D will hold the helper flag.
- Put a heading such as Visible in D1.
- With the record IDs in C2:C8, enter
=SUBTOTAL(103,$C2)in D2 and fill it down through D8. The fixed column keeps the reference on the ID field as the formula is copied; the row changes for each record. - With the region to count in E2, enter
=SUMIFS($D$2:$D$8,$A$2:$A$8,$E$2)in another cell. It sums the flags only for rows whose region matches E2. - Filter the data using Data > Filter, or use the filter controls on an Excel table. The helper flags recalculate for the rows that remain visible.
Suppose rows 2 through 8 have regions East, West, East, East, North, West, and East. Their statuses are Open, Open, Closed, Open, Open, Closed, and Open. If the filter shows only Open orders and E2 contains East, the result is 3: the visible East orders are in rows 2, 5, and 8. COUNTIF applied to the full region range would return 4 because it also counts the East order in filtered-out row 4.
The helper formula relies on C being populated in every record row. If IDs can be blank, choose a different column that is guaranteed to contain a value, such as a required transaction number. If there is no reliable nonblank field, a simple COUNTA-based visibility flag cannot represent every record.
Single-cell alternative with SUMPRODUCT
If you prefer not to add a helper column, SUMPRODUCT can combine a row-by-row visibility check with a condition. This version uses column C as the always-filled record ID, column A for the region, and E2 for the region to count:
With IDs in C2:C8, regions in A2:A8, and the requested region in E2, enter =SUMPRODUCT(SUBTOTAL(103,OFFSET($C$2,ROW($C$2:$C$8)-ROW($C$2),0)),--($A$2:$A$8=$E$2)). For the example with Open rows visible and East in E2, it returns 3.
Here, OFFSET supplies a separate one-cell reference for each row. SUBTOTAL returns a count for a visible, nonblank ID and zero for a filtered-out row; the comparison against E2 produces a match or non-match for each region. SUMPRODUCT combines those results into one count. Because the visibility check uses the ID column, this formula can count a matching blank region if one exists, as long as its record ID is filled.
This formula is more compact on the worksheet, but less transparent to someone maintaining it. OFFSET is a volatile function, meaning Excel recalculates it more frequently than many ordinary formulas. For a very large dataset or a workbook where clarity matters, the helper-column version is often easier to audit. Both approaches use functions available in Microsoft 365 for Windows.
Choosing function numbers and counting hidden rows
Choose the SUBTOTAL function number based on what the referenced cells contain and whether manually hidden rows should count:
- Use 3 or 103 to count nonblank cells, including text and numbers.
- Use 2 or 102 to count numeric cells only.
- Use 3 or 2 when manually hidden rows should be included.
- Use 103 or 102 when manually hidden rows should be excluded as well as filtered-out rows.
For a criteria count, the chosen number also affects the helper flag or the visibility test in the single-cell formula. If the record field contains text IDs, use 3 or 103; a numeric-only count could incorrectly return zero for those IDs. Filtered-out rows are excluded with either version of the function number.
One subtle issue is that COUNTA treats a cell containing a formula that returns an empty string as nonblank. If your supposed ID field uses formulas that display as empty strings, SUBTOTAL with 3 or 103 can still regard those cells as populated. Base the visibility check on a field that is genuinely populated for each record, or adjust the design to suit how the data is stored.
Tables, filters, and practical setup
Excel tables make this workflow easier to use as data grows. Select the dataset and choose Insert > Table, ensuring the option indicating that the table has headers is appropriate. Tables provide filter controls in the header row, and their formulas can fill down when new rows are added. A helper formula inside a table can therefore be maintained alongside the records.
Keep the helper column inside the same filtered dataset. If you filter only some columns or apply a filter to a range that does not include all records, the visible rows may not correspond to the records you intend to count. Remove or update existing filters before diagnosing a surprising result, and check that the criteria range and helper range cover the same rows.
On Microsoft 365 for Windows, the Data tab contains the Filter command. Excel for Mac and Excel for the web also provide filtering through the Data tab, though the ribbon layout can vary by platform and window size. The formulas themselves do not require a different Mac or web syntax. If a filter is applied to a table, use its header dropdowns to change which rows are visible.
Common errors and ways to check the result
- The count does not change after filtering: Check that the formula uses SUBTOTAL for its visibility test. A plain COUNTIF or COUNTIFS counts matching cells in hidden rows too.
- The result is lower than expected: Confirm the helper checks a filled record field and uses the intended count type. A numeric COUNT will not count text IDs.
- Manually hidden rows behave unexpectedly: Decide whether they should count. Use function number 3 to include them or 103 to exclude them when counting nonblank IDs.
- The result is too high or too low: Verify that every range starts and ends on the same rows, and that the criteria cell contains the exact value you intend to match.
- Blank or formula-generated values cause confusion: Check whether cells are truly empty or contain formulas returning empty strings. COUNTA can count the latter as nonblank.
A useful check is to filter the list to a small, easily counted set of records. Compare the helper flags on screen with the result from SUMIFS. You can also temporarily remove the filter and confirm that the count changes to the total number of matching records. In the example, the unfiltered East count is 4 and the Open-only East count is 3.
How this differs from COUNTIFS
COUNTIFS can apply more than one condition, such as counting rows where Region is East and Status is Open. However, like COUNTIF, it counts matching cells in filtered-out rows. Adding a second condition does not make it filter-aware.
If multiple criteria are needed, the helper-column approach can extend naturally: SUMIFS can sum the visible flags using several criteria ranges and criteria. The one-cell SUMPRODUCT approach can also multiply additional condition tests into the calculation, but it becomes harder to read as conditions accumulate. For a report that other people will update, labeled helper columns are often preferable to a dense formula.
Frequently Asked Questions
Does COUNTIF ignore filtered rows in Excel?
No. COUNTIF evaluates the cells in its specified range even when some rows are hidden by a filter. Use a SUBTOTAL-based visibility check with SUMIFS or SUMPRODUCT if the answer should include only visible records.
Can I use SUBTOTAL with a criterion such as East?
Not directly. SUBTOTAL selects a calculation such as counting or summing, but it does not accept a condition argument. Combine a visibility check with a criteria test, as in the helper-column or SUMPRODUCT methods above.
Should I use 3 or 103?
Both count nonblank cells and exclude filtered-out rows. Use 3 if manually hidden rows should remain included; use 103 if manually hidden rows should also be excluded.
Why does my visible-row count include blank-looking cells?
A cell may contain a formula that returns an empty string rather than being truly empty. COUNTA-based SUBTOTAL can count that formula cell. Test the field used for the visibility flag and, if needed, use a reliably populated record ID column instead.
Can I count visible rows that meet two conditions?
Yes. With a helper flag, use SUMIFS with two criteria ranges and criteria, such as region and status. The helper flag supplies the visible-row weighting; the criteria ranges select the records of interest.
Related
- Excel Sum Duplicates: How to Total Repeated Values
- Excel: Sum a Filtered Column with SUBTOTAL
- Excel Sum Above: How to Add Cells Above Your Total
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel SUBTOTAL: Count Unique Values in Filtered DataExcel’s SUBTOTAL function cannot count unique values by itself. In Microsoft 365, combine UNIQUE and FILTER with SUBTOTAL…
- Excel XLOOKUP Examples: Formulas and TipsMicrosoft Excel’s XLOOKUP finds a value in one range and returns the corresponding value from another; for example,…
- Find in Excel: Search Cells, Data, and FormulasTo find text, numbers, or other content in Microsoft Excel for Microsoft 365 on Windows, press Ctrl+F, enter…
- NPV Analysis in Excel: Formula, Steps, and ExampleTo calculate net present value in Excel, enter the discount rate in one cell, put the initial investment…
- Power Query Sort: How to Sort and Keep Rows in OrderTo sort data in Power Query in Excel for Microsoft 365 on Windows, open the query, select the…
- SUMIFS Excel Less Than: Criteria and ExamplesTo sum values in Excel only when a related value is less than a limit, use SUMIFS with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.