Google-sheets – Create serial count in column if adjacent cell is blank (Google Sheets)

google sheets

I'm trying to create a simple counting ArrayFormula that iterates by one, if adjacent cell is empty (doesn't contain "IGNORE"). Eg COUNT column here should only count when STATUS <> IGNORE:

ROW   STATUS    COUNT
______________________
1     IGNORE    
2     IGNORE
3                 1
4                 2
5                 3
6     IGNORE
7     IGNORE
8                 4
9                 5

What ArrayFormula can I use here in cell C1 (COUNT)?

Any ideas greatly appreciated!

Best Answer

Delete everything in Column C (including the header) and then place the following formula in C1:

=ArrayFormula({"COUNT";IF(B2:B<>"",,COUNTIFS(B2:B,"",ROW(B2:B),"<="&ROW(B2:B)))})

The curly brackets form a virtual array with the header "COUNT" placed above the results.

IF(B2:B<>"",, leaves a cell blank if the adjoining cell in Col B is not blank.

In the remaining cells (those beside a blank in Col B), COUNTIFS will keep a running count of all cells in B2:B that are blank and whose row number is less than or equal to the current row number as it goes.