Count incremental blank cells till next non empty cell (Formulas Only)

1
votes

My data looks like below. I want to count consecutive blank cells till next non empty cell. My logic is to get output, when A2 contains data then it will return 0 but when A3 is blank then I expect 1 and continue to count blank cells incrementally.

Part_No Location    Expected_Output
ABC123  North          0
        South          1
XYZ123  East           0
        South          1
        West           2
PQR123  South          0
        East           1
        North          2
        West           3
BROF-2  South          0
        East           1

Screenshot as well
enter image description here

Hope someone will help me to solve my problem. Thanks.

2
excelexcel-formula

2 Answers

3
votes

Try this if the blanks are in coulmn A

 =COUNTBLANK(INDIRECT("A"&LOOKUP(2,1/($A$2:A2<>""),ROW($A$2:A2))&":A"&ROW()))
2
votes

In Range C2, use the formula:

=IF($A2<>"",0,$C1+1)

and then copy down- should do the trick.

Hope this helps, TheSilkCode