To count numeric cells with Excel’s SUBTOTAL function, use =SUBTOTAL(2,A2:A100); to count nonblank cells, use =SUBTOTAL(3,A2:A100). Both formulas ignore rows removed by a filter. Use function number 102 or 103 instead when you also want to exclude rows hidden manually.
What SUBTOTAL counts in Excel
SUBTOTAL is a worksheet function for calculating a result across a range, with special handling for filtered and hidden rows. Its first argument, called the function number, tells Excel what calculation to perform. For counting, the important choices are 2 and 3, or their 100-series counterparts, 102 and 103.
Function number 2 performs the same basic count as COUNT: it counts cells containing numbers. Function number 3 performs the same basic count as COUNTA: it counts cells that are not empty. The difference from using COUNT or COUNTA directly is that SUBTOTAL responds to filtering. When you filter a list, rows excluded by the filter do not contribute to the subtotal.
This makes SUBTOTAL useful for questions such as “How many visible orders are in this filtered list?” It does not mean “count every cell that looks like a record” unless you choose the right range and counting rule. A column of numeric order IDs, for example, can be counted with function number 2; a column of names or text labels generally needs function number 3.
Which function number should you use?
The function number determines both what is counted and how manually hidden rows are treated:
- 2: Counts numbers, including dates and times stored as numbers. Filtered-out rows are excluded, but manually hidden rows are included.
- 3: Counts nonempty cells of any type. Filtered-out rows are excluded, but manually hidden rows are included.
- 102: Counts numbers, excluding both filtered-out rows and rows hidden manually.
- 103: Counts nonempty cells, excluding both filtered-out rows and rows hidden manually.
The 100-series numbers are not different counting rules; they add the instruction to disregard manually hidden rows. Both sets of function numbers disregard rows removed by a filter. If a row is hidden with the Hide command or by grouping, the distinction between the standard and 100-series numbers matters.
Examples: count numbers or nonblank cells
Suppose A2:A7 contains the values 12, a blank cell, the text Pending, 18, 0, and another blank cell. With those values in A2:A7, this formula counts numeric entries:
=SUBTOTAL(2,A2:A7)
The result is 3: Excel counts 12, 18, and 0. Zero is a number, so it is counted. The text Pending and the blank cells are not counted by function number 2.
With the same values in A2:A7, this formula counts nonempty entries instead:
=SUBTOTAL(3,A2:A7)
The result is 4: the three numbers and the text Pending. A genuinely empty cell is not counted. This distinction is helpful when a column contains a mixture of text and numbers and your goal is to count filled records rather than numeric values specifically.
For a list of customer names in A2:A100, use function number 3 because names are text. For a list of quantities in C2:C100, use function number 2 if only numeric quantities should count. Put the subtotal formula outside the range being counted—for example, in A101 when counting A2:A100—so the result is easy to see and the formula does not include itself.
How filtering changes the count
To see SUBTOTAL’s filtering behavior, apply a filter to a list and select only some of its rows. In Excel for Microsoft 365 on Windows, select a cell in the list and choose Data > Filter. Use the drop-down arrow in a column heading to select the values to display. SUBTOTAL recalculates using the rows still visible after that filter is applied.
For example, assume A2:A6 contains five order IDs and B2:B6 contains each order’s status. With order IDs in A2:A6, this formula counts the visible IDs after you filter the status column:
=SUBTOTAL(3,A2:A6)
If the filter leaves three orders visible and each visible ID cell is filled, the result is 3. Clear the filter, and the result returns to 5. The range should cover a column that is populated once per record; counting a field that is sometimes blank can understate the number of visible records.
The filter is not part of the formula’s written range criteria. SUBTOTAL simply detects which rows a filter has hidden and excludes them. This is why it is a convenient live count for interactive reports: readers can change the filter without editing the formula.
Filtered rows versus manually hidden rows
Filtering and manually hiding rows are different actions in Excel. A row excluded by AutoFilter is ignored by both function number 3 and function number 103. A row hidden manually is still included by function number 3, but excluded by function number 103. The same distinction applies to numeric counts with 2 and 102.
For example, if A2:A6 contains five nonblank labels and you manually hide row 4, a formula using function number 3 still counts all five labels. With the same range, function number 103 returns 4. If instead a filter hides one of those rows, both formulas return 4.
Choose based on what “visible” means in your report. Use 3 or 2 when you want a filter-responsive subtotal but still want manually hidden rows included. Use 103 or 102 when any hidden row—filtered or manually hidden—should be left out. This behavior is especially important in worksheets where people hide rows to simplify the view.
Choosing between numeric and nonblank counts
Start by deciding what makes a cell count as a record. Use the numeric version if only numbers should qualify; use the nonblank version if any entered value should qualify. Dates and times count as numeric values in Excel because they are stored as serial numbers. Text that looks like a number may not count as numeric if it is stored as text.
- Count entered quantities, amounts, or numeric IDs: choose 2 or 102.
- Count names, labels, or mixed text-and-number entries: choose 3 or 103.
- Count records after a filter: use either suitable counting rule; filtered-out rows are excluded.
- Also exclude rows hidden manually: choose the matching 100-series number.
Be careful with cells that contain formulas returning an empty string. COUNTA-style counting treats a cell with a formula as nonempty even if the displayed result looks blank. Therefore, function numbers 3 and 103 can count such a cell. A cell containing only spaces is also not truly empty. If those cases occur in the column you are counting, choose a reliably populated key column or review how the source data is constructed.
Common mistakes and troubleshooting
- Using 2 for a text column: Names and labels are not numeric values, so a numeric count can return zero or a smaller number than expected. Use 3 or 103 for nonblank text.
- Expecting 3 to ignore manually hidden rows: It does not. Switch to 103 if manually hidden rows should be excluded.
- Counting a column with gaps: SUBTOTAL counts qualifying cells in the selected range, not abstract records. Use a column that has an entry for each record.
- Including the result cell in the range: Keep the subtotal formula outside its own counted range. SUBTOTAL also ignores SUBTOTAL formulas within a referenced range, which helps prevent nested subtotals from being counted again.
- Confusing zero with blank: Zero is a numeric value and is counted by 2 or 102. A blank cell is not.
If the result seems wrong, first check the exact range, then check whether the relevant cells are numeric, text, truly blank, or formulas returning empty text. Next confirm whether rows were filtered or hidden manually. These checks usually explain a mismatch without requiring a more complicated formula.
SUBTOTAL compared with COUNT and COUNTA
COUNT and COUNTA are straightforward when every row should be counted regardless of visibility. COUNT counts numbers; COUNTA counts nonempty cells. They do not provide SUBTOTAL’s automatic exclusion of filtered-out rows. If a user changes a filter and the displayed record count should update, SUBTOTAL is usually the more suitable choice.
COUNTIF and COUNTIFS are useful when you need to count cells matching conditions, such as a particular status. However, those functions do not automatically become visible-row counts just because a filter is applied. SUBTOTAL is designed for visibility-aware subtotals, while conditional-count functions address criteria. If a task requires both filtering visibility and additional conditions, consider whether the data layout or a more specialized formula is needed rather than assuming COUNTIFS will behave like SUBTOTAL.
SUBTOTAL can also calculate sums, averages, and other operations, but its counting options are specifically 2, 3, 102, and 103. It is not a universal replacement for every counting formula: the right choice depends on whether you need a numeric count, a nonblank count, visibility handling, or criteria-based counting.
Frequently Asked Questions
What is the SUBTOTAL formula to count visible cells?
For nonblank cells, use function number 3; for numeric cells, use function number 2. Both exclude rows removed by a filter. Choose 103 or 102, respectively, if manually hidden rows should also be excluded.
Does SUBTOTAL count blank cells?
No. The COUNT-style options 2 and 102 count numeric values, while the COUNTA-style options 3 and 103 count nonempty cells. A formula that displays an empty string may still be counted by the nonblank options.
Does SUBTOTAL ignore manually hidden rows?
Not with function numbers 1 through 11. Use the corresponding number from 101 through 111 to exclude manually hidden rows as well as filtered-out rows. For counting, that means 102 or 103.
Why does SUBTOTAL return a different count from COUNTA?
COUNTA includes nonempty cells in hidden or filtered rows, while SUBTOTAL excludes filtered-out rows. Also check that both formulas cover the same range and that cells that look blank do not contain spaces or formulas returning empty text.
Can SUBTOTAL count visible text entries?
Yes. Use function number 3 for nonblank text entries that remain visible after filtering, or 103 if manually hidden rows should be excluded too. Make sure the chosen column contains a value for each record you intend to count.
Related
- Excel: Sum a Filtered Column with SUBTOTAL
- Excel Sum Above: How to Add Cells Above Your Total
- Excel Max Rows per Sheet: Limits and Workarounds
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Text to Columns Using Enter: Split Line BreaksTo split a cell at an embedded line break with Excel Text to Columns, choose Data > Text…
- How to Split Cells in Excel Vertically: Text and LayoutTo split text in an Excel cell into side-by-side columns, select the cells, then choose Data > Text…
- Adding a Checkbox in Google Sheets: Steps and UsesTo add a checkbox in Google Sheets in a web browser, select the cell or range where you…
- How to Create and Add a Signature Stamp in OutlookTo create a signature stamp in Outlook, prepare an image of your handwritten signature, add it to an…
- Unable to Create Rules in New Outlook? Fixes That WorkYes, you can create email rules in new Outlook, but the option may be missing or unavailable because…
- Excel SUM and Hidden Cells: What Gets Included in Totals?Yes. In Excel for Microsoft 365 on Windows, SUM includes numbers in hidden rows and columns, including rows…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.