The subtotal number you use with COUNTIF is none—COUNTIF is not a Subtotal function. If you meant “which Subtotal function number counts cells,” the answer is 2 (COUNT) or 102 (COUNTA, ignoring hidden rows). But if you are trying to count cells that meet a condition within a filtered list, you need SUMPRODUCT with SUBTOTAL offsets, not a single number.
Understanding Excel’s Subtotal Function Numbers
Excel’s SUBTOTAL function uses a numeric code (1–11 or 101–111) to tell it which aggregate to perform. The first set (1–11) includes manually hidden rows; the second set (101–111) ignores rows hidden by a filter or manually. The codes are:
- 1/101 – AVERAGE
- 2/102 – COUNT (numbers)
- 3/103 – COUNTA (non-empty)
- 4/104 – MAX
- 5/105 – MIN
- 6/106 – PRODUCT
- 7/107 – STDEV
- 8/108 – STDEVP
- 9/109 – SUM
- 10/110 – VAR
- 11/111 – VARP
So if you want a subtotal that counts numeric cells, use 2 or 102. For counting non-empty cells (text or numbers), use 3 or 103. The number 9 is for summing, not counting—a common mix-up.
Why COUNTIF Doesn’t Work Directly with SUBTOTAL
COUNTIF counts cells that meet a condition, but it ignores filters. For example, if you have a column with values and you filter to show only “Apple,” a COUNTIF formula still counts all “Apple” cells in the entire range, including hidden ones. That’s because COUNTIF does not have a built-in “ignore hidden” option. The SUBTOTAL function can ignore hidden rows, but it cannot apply a condition. To combine both, you must use a workaround.
How to Count with a Condition on Filtered Data
The standard solution is to use SUMPRODUCT with SUBTOTAL and OFFSET. Here’s a concrete example:
Assume your data is in A2:A20, with values like “Apple”, “Banana”, “Apple”. You filter the range to show only rows where column B equals “Yes”. To count how many “Apple” cells are visible:
- In cell D1, enter:
=SUMPRODUCT((A2:A20="Apple")*(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),0)))) - Press Enter. The result is the count of visible “Apple” cells.
How it works: OFFSET creates a separate range for each row, SUBTOTAL(3,...) (COUNTA) returns 1 if the row is visible, 0 if hidden. Multiplying by the condition (1 if “Apple”) and summing gives the visible count.
For the example with A2:A20 containing 5 “Apple” cells, and 3 of them visible after filtering, the formula returns 3.
Alternative: Use a Helper Column
If the SUMPRODUCT formula feels too complex, add a helper column. In C2, enter =SUBTOTAL(3,A2) and copy down. This returns 1 for visible rows, 0 for hidden. Then use COUNTIFS with two criteria: =COUNTIFS(A2:A20,"Apple",C2:C20,1). This counts rows where column A is “Apple” and the helper column shows visible. This is easier to read and debug.
Common Mistakes and Misconceptions
Many users try =SUBTOTAL(2, A2:A20) thinking it will count only filtered cells—and it does count visible numeric cells, but it cannot apply a condition. Others mistakenly use 9 (SUM) when they want a count. Also, note that SUBTOTAL ignores other SUBTOTAL formulas inside its range to avoid double counting, but that does not help with conditions.
Another misconception: COUNTIF with a filter does not automatically ignore hidden rows. There is no direct COUNTIF variant for filtered data. You must use the methods above.
Excel for Mac and Web Differences
The formulas above work identically in Excel for Microsoft 365 on Windows, Mac, and Excel for the web. The SUBTOTAL function and SUMPRODUCT are available in all versions. The only difference is that on Mac, you press Cmd+Enter to enter an array formula (though SUMPRODUCT does not require array entry). Keyboard shortcuts for filtering: Ctrl+Shift+L on Windows, Cmd+Shift+L on Mac.
When to Use SUBTOTAL vs. AGGREGATE
Excel’s AGGREGATE function (available since Excel 2010) also has function numbers. For counting, use 2 or 3 (ignore hidden rows) or 12 or 13 (ignore errors and hidden rows). But AGGREGATE still cannot apply a condition directly. So the same workaround applies.
Frequently Asked Questions
What subtotal number counts only visible cells?
Use 102 for numbers or 103 for non-empty cells. These ignore rows hidden by filtering. For example, =SUBTOTAL(103,A2:A20) counts all non-empty visible cells in A2:A20.
How do I count unique visible values?
Use SUMPRODUCT with COUNTIF and SUBTOTAL. For visible unique count in A2:A20, use: =SUMPRODUCT((SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),0))>0)/COUNTIF(A2:A20,A2:A20)). This is an array formula; press Ctrl+Shift+Enter on Windows, Cmd+Shift+Enter on Mac.
Can I use COUNTIF with a filter directly?
No, COUNTIF ignores filters. Use SUMPRODUCT with SUBTOTAL or a helper column.
Why does my SUBTOTAL(9,…) return a sum, not a count?
Because 9 is the code for SUM. For count, use 2 or 102. Check the function number table above.
Does SUBTOTAL work with text?
Yes, SUBTOTAL(103,...) counts non-empty cells including text. SUBTOTAL(102,...) only counts numeric cells.
What’s the difference between 2 and 102?
2 includes rows hidden manually, while 102 ignores rows hidden by a filter or manually. In most cases, use 102 to count only visible filtered rows.
Related
- Excel Strikethrough: How to Apply & Remove It
- Excel Strikethrough: How to Add It & Shortcuts
- Excel Utility for E-Invoice: What It Does
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFS in Conditional Formatting: How to Use ItYes. In Excel for Microsoft 365 on Windows, you can use the IFS function in a conditional-formatting formula,…
- Excel Filter Drop-Down Shortcut: Open It QuicklyIn Excel for Microsoft 365 on Windows, select a cell in the filtered column’s header row and press…
- Filter Excel Rows by Cells That Contain Specific TextTo filter Excel rows by text inside a cell, turn on AutoFilter, open the filter arrow in the…
- Split Function in Appian: Syntax and ExamplesThe split() function in Appian divides a text value into a list of substrings based on a delimiter,…
- Copy Formulas in Excel Without Changing ReferencesTo copy a formula without letting Excel adjust its cell references, lock the references with dollar signs before…
- Excel COUNTIF Date in Cell? Formula & ExamplesTo count cells containing a specific date in Excel, use =COUNTIF(range, date) where date is a date entered…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.