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.