2

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!

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.