excelmatchblank-line

Excel: Count consecutive cells that are not empty

I would like to identify which rows don't have consecutive data. For this, I'd like to be able to count the cells across rows until and empty cell is encountered.

Please look at the the screenshot of an example and desired output. enter image description here

I have tried to play around with the MATCH function, but no look yet. I've tried the following but it's not returning what I would've expect.

{=MATCH(FALSE,ISBLANK(C2:F2),0)}
Solution

  • You may try in this way however there might be a more eloquent way to solve this,

    So the formula used in cell G2

        =IF(MAX(FREQUENCY(IF(C2:F2,COLUMN(C2:F2)),
IF(C2:F2=FALSE,COLUMN(C2:F2))))<3,"Jumps A Year",
MAX(FREQUENCY(IF(C2:F2,COLUMN(C2:F2)),
IF(C2:F2=FALSE,COLUMN(C2:F2)))))

    And Fill Down!

    Also note, depending on your excel version you may or may need to press CTRL+SHIFT+ENTER, not just ENTER after entering the formula. You will know the array is active when you see curly braces { } appear around your formula.

    Count consecutive cells that are not empty