Numerar Celdas: En Excel Con Condiciones

that also ignores blanks:

Mastering COUNTA , SUBTOTAL , and COUNTIFS for numbering teaches a deeper lesson: Each cell is a pure function (or should be) of the cells above it. Conditional numbering forces the user to think in terms of state , scope , and visibility —concepts usually reserved for software engineering.

Enter SUBTOTAL with function number 103 (or 3 for classic counting). The formula is:

=LET( visible, SUBTOTAL(103, A2), group, A2, IF(visible, COUNTIFS(A$2:A2, group, SUBTOTAL(103, OFFSET(A$2, ROW(A$2:A2)-ROW(A$2), 0)), 1), "") ) (This is a conceptual simplification; the actual implementation often requires helper columns for performance.)

The principle is sound: you must create a helper column that marks visibility ( =SUBTOTAL(103, A2) ), then use COUNTIFS on that helper column. This pushes Excel to its logical limits. To number cells with conditions is to understand that spreadsheets are not merely ledgers but interactive models. The simple fill handle sees no difference between a data row and an empty spacer. The conditional formula, however, sees context: blanks, filters, categories.

This mimics the behavior of a for loop in programming without VBA. The formula carries its own history. It is stateful —each cell’s output depends on the count of previous cells. This is the foundation of running totals and ranked lists. However, it fails catastrophically with filters or hidden rows, because COUNTA sees hidden cells. 2. The Invisible Condition: Numbering Filtered Data When you apply a filter to a table, rows become hidden. A standard COUNTA formula will break the sequence, creating gaps (e.g., 1, 2, 5, 7). The user needs a numbering system that sees only the visible universe.

Thus, the next time you need to number a list, do not drag the fill handle. Ask: What is the condition? If the answer is “just count everything,” use the fill handle. But if the answer involves “except,” “only if,” “per group,” or “when visible,” you have entered the realm of conditional numbering—where formulas become algorithms, and rows become records.

=IF(SUBTOTAL(103, A2)=1, SUBTOTAL(103, A$2:A2), "")