Subtotal Formula to Count Cells: Excel Guide

Formulas & functions · Updated November 22, 2026

The formula to count cells with the SUBTOTAL function is =SUBTOTAL(2, range) for a simple count, or =SUBTOTAL(103, range) to count only visible cells after filtering. The first argument (2 or 103) is the function number that tells SUBTOTAL to perform a COUNTA operation, and the second is the range you want to count.

Understanding the SUBTOTAL Function Numbers

SUBTOTAL is a versatile function that can perform 11 different operations (average, sum, min, max, etc.) depending on the first argument. For counting, you use function number 2 (COUNTA) or 3 (COUNT). The difference matters:

  • 2 (COUNTA) – counts all non-empty cells, including text, numbers, and errors. It ignores truly empty cells.
  • 3 (COUNT) – counts only cells containing numeric values. Text and errors are ignored.
  • 102 and 103 – same as 2 and 3, but they ignore rows hidden by filtering. They do not ignore rows hidden manually (right-click > Hide).

For most counting tasks, =SUBTOTAL(103, A2:A100) is the go-to because it gives you a live count that updates when you filter your data.

How SUBTOTAL Differs from COUNT and COUNTA

The regular =COUNTA(A2:A100) counts every non-empty cell in the range, whether visible or hidden. SUBTOTAL with 103 does the same, but it automatically recalculates to exclude rows hidden by a filter. This is the key advantage: when you apply an AutoFilter and hide rows, SUBTOTAL(103) instantly shows the count of only the visible rows. The regular COUNTA would still show the total of all rows, including hidden ones.

Another difference: SUBTOTAL ignores other SUBTOTAL formulas inside its range, which prevents double-counting when you have nested subtotals (e.g., in a data outline).

Using SUBTOTAL with Filtered Data: Step-by-Step

Here’s a practical example to see it in action:

  1. Assume you have a list of orders in A2:A50, with some cells blank.
  2. Select the data range and go to Data > Filter (or press Ctrl+Shift+L on Windows, Cmd+Shift+L on Mac) to add filter arrows.
  3. In a cell outside the data (e.g., C1), type =SUBTOTAL(103, A2:A50) and press Enter.
  4. The result shows the total number of non-empty cells in A2:A50.
  5. Now click a filter arrow and uncheck some values (e.g., only show “Pending” orders). The SUBTOTAL formula instantly updates to show only the visible, non-empty cells.

If you want to count numeric values only, replace 103 with 3: =SUBTOTAL(3, A2:A50).

Common Mistakes and How to Avoid Them

Several pitfalls trip up users when using SUBTOTAL to count:

  • Using 2 instead of 103 – Function number 2 ignores only manually hidden rows? No, actually 2 ignores nothing; it counts all cells regardless of visibility. Only 102 and 103 respond to filtering. If you want filtered counts, you must use 102 or 103.
  • Including the header row – If your range includes a header, SUBTOTAL will count it as a non-empty cell. Start your range from the first data row (e.g., A2, not A1).
  • Hidden rows via right-click – SUBTOTAL with 103 does not ignore rows hidden by manual row hiding (right-click > Hide). It only ignores rows hidden by AutoFilter or table filters. If you need to count excluding manually hidden rows, you’d need a different approach (e.g., using AGGREGATE with option 5).
  • Forgetting to update the range – If you add rows below the range, the formula won’t include them. Use a structured table (Insert > Table) so the range expands automatically, or use a full-column reference like A:A (but be cautious with performance).

SUBTOTAL vs. AGGREGATE for Counting

Excel’s AGGREGATE function (available since Excel 2010) offers more control. Its syntax is =AGGREGATE(function_num, options, range). For counting visible cells, you can use function 2 (COUNTA) with option 5 (ignore hidden rows): =AGGREGATE(2, 5, A2:A50). This also ignores error values if you choose option 6. The main difference: AGGREGATE lets you ignore error values and manually hidden rows, which SUBTOTAL cannot. However, SUBTOTAL is simpler and more widely understood. For most users, SUBTOTAL(103) is sufficient.

Counting with Criteria: Combining SUBTOTAL and SUMPRODUCT

SUBTOTAL alone cannot count cells that meet a specific condition (e.g., count only “Yes” in visible rows). To do that, you need a helper column or SUMPRODUCT with SUBTOTAL’s hidden function. A common trick uses the OFFSET function to create a range of visible statuses:

Assume data in A2:A50 (values) and B2:B50 (criteria like “Yes”/“No”). In C1, enter:

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A50, ROW(A2:A50)-MIN(ROW(A2:A50)), 0, 1)), --(B2:B50="Yes"))

This array-like formula counts visible rows where column B equals “Yes”. It works by using OFFSET to evaluate each row individually. Press Ctrl+Shift+Enter in older Excel versions; in Excel 365, it works as a dynamic array. This is advanced, but it’s the standard solution for filtered conditional counts.

Frequently Asked Questions

Does SUBTOTAL(103) count blank cells? No. It counts non-empty cells. If you want to count blanks among visible rows, use =SUBTOTAL(103, range) on an empty range? Actually, you’d need to count total rows minus non-empty. For example, =ROWS(A2:A50)-SUBTOTAL(103, A2:A50) gives the count of visible blank cells.

Why does my SUBTOTAL formula return 0? Check if the range is truly empty or if you used function 3 (COUNT) on text values. Also, ensure the range doesn’t include only hidden rows – if all rows are filtered out, the count is 0.

Can I use SUBTOTAL in a pivot table? No, SUBTOTAL is a worksheet function. Pivot tables have their own subtotal feature (right-click > Subtotal). But you can use SUBTOTAL on the source data before creating the pivot.

What’s the difference between SUBTOTAL(2) and SUBTOTAL(102)? Function 2 counts all non-empty cells regardless of filtering. Function 102 counts only visible (filtered) non-empty cells. So 102 is the filtered version of 2, and 103 is the filtered version of 3.

Does SUBTOTAL work on filtered tables? Yes, it works on Excel Tables (Insert > Table) as well. When you filter a table, SUBTOTAL(103) updates automatically. However, note that table structured references (e.g., Table1[Column1]) work fine with SUBTOTAL.

Can I use SUBTOTAL to count unique visible values? No, SUBTOTAL cannot count unique values. You’d need a combination of SUMPRODUCT and COUNTIF, or use the newer UNIQUE function with FILTER (Excel 365) to get unique visible values, then count them with COUNTA.

Practical Tips for Real-World Use

When building a dashboard or report, place your SUBTOTAL formula in a cell that won’t be filtered out. For example, put it above the data range or in a separate summary area. Also, remember that SUBTOTAL recalculates automatically when you change filters, but if you have manual calculations set to manual (Formulas > Calculation Options), you may need to press F9 to refresh.

If you’re using Excel for the web or Mac, the function works identically, but keyboard shortcuts differ slightly: on Mac, the filter shortcut is Cmd+Shift+L, and the function is the same. No special syntax needed.

Finally, if you need to count visible cells that meet multiple criteria, consider using the AGGREGATE function with SUMPRODUCT, or upgrade to Excel 365’s FILTER function: =COUNTA(FILTER(A2:A50, (B2:B50="Yes")*(ISNUMBER(SUBTOTAL(103, OFFSET(A2:A50, ROW(A2:A50)-1, 0, 1)))))). This is more complex but gives you full control.

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.