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.
that also ignores blanks:
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). numerar celdas en excel con condiciones