The range A$2:A2 is the key. As the formula is copied down, the top anchor remains fixed (A$2), while the bottom expands (A2 becomes A3, A4, etc.). The COUNTA function counts only non-blank cells in this expanding window. Because the IF statement checks the current row first, only rows with data receive a number. The blanks receive an empty string, preserving the visual hierarchy.
The solution lies in a counter-intuitive use of COUNTIF or COUNTA with a mixed reference. In cell B2, you enter: numerar celdas en excel con condiciones
This is a form of window function (similar to ROW_NUMBER() OVER (PARTITION BY Category) in SQL). It demonstrates that Excel’s grid can perform relational database operations without a database engine. This technique is invaluable for creating outlines, bill of materials (BOM) exploded views, or numbered lists inside pivot table source data. 4. The Advanced Synthesis: Combining Visibility and Hierarchy The ultimate challenge: number visible rows only, restarting the count per group, after a filter. This requires an array formula (or the new LET and FILTER functions in modern Excel). The range A$2:A2 is the key
This counts how many times the current category value has appeared so far in the expanding range. When the category changes (e.g., from “Fruit” to “Vegetables”), the count resets to 1. This creates perfect nested numbering: Fruit: 1, 2, 3; Vegetables: 1, 2; Dairy: 1. Because the IF statement checks the current row
=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.)
This requires COUNTIFS (or SUMIFS with a logical trick). Assume Column A is Category, Column B is Item. In C2:
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.