Conditional Aggregation
Count Visible Rows & Filtered Criteria Matches (SUBTOTAL 103 & Helper Column)
Count visible non-empty records with SUBTOTAL(103, range) and pair a row-level visibility indicator (=SUBTOTAL(103, A2)) with SUMPRODUCT to count visible rows matching specific criteria.
Why standard COUNTIF still counts hidden filtered rows—and how to fix it!
COUNTA and COUNTIF always scan hidden and AutoFiltered rows! Function_num 103 inside SUBTOTAL(103, A2:A5) counts ONLY visible non-empty cells (ignoring both AutoFiltered rows and manually hidden rows, whereas code 3 ignores AutoFiltered rows only).
When you need to count visible rows that ALSO match a criterion (such as Region = "EMEA"), add a lightweight Is_Visible helper column C holding =SUBTOTAL(103, A2) on each row (which returns 1 when the row is visible and 0 when hidden), then evaluate =SUMPRODUCT(--(B2:B5 = "EMEA"), C2:C5)!
| Order ID | Region | Visible Flag C_i | Included in Visible EMEA? |
|---|---|---|---|
| ORD-101 | EMEA | 1 (Visible) | Yes (1) |
| ORD-102 | APAC | 1 (Visible) | No (APAC) |
| ORD-103 | EMEA | 0 (Hidden Row) | No (Hidden) |
| ORD-104 | EMEA | 1 (Visible) | Yes (2) |
Visible Rows Matching Target Region (Helper-Flag SUMPRODUCT)
Multiplies the region match indicator --(B2:B5 = B6) by the row visibility flag C2:C5 (where 1 = visible, 0 = hidden).
=IF(LEN(TRIM(B6)) = 0, 0, SUMPRODUCT(--(B2:B5 = B6), C2:C5))Total Non-Empty Visible Rows via SUBTOTAL(103, ...)
Counts visible non-blank Order IDs in A2:A5 directly on the active worksheet.
=SUBTOTAL(103, A2:A5)Try the example
Use the original values or change them to test your understanding.