Find Consecutive months without counting empty cell in the middle

[tzianhao]

[TABLE="width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1803M[/TD]
[TD]1804M[/TD]
[TD]1805M[/TD]
[TD]1806M[/TD]
[TD]1807M[/TD]
[TD]1808M[/TD]
[TD]1809M[/TD]
[TD]Remarks[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]First Month[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2 Consecutive months
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3 Consecutive months [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4 Consecutive months[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]5 Consecutive months [/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to count a value consecutively with counting the empty cells in the middle ?
The return I want is like Column H where when there's an empty cell, it recounts from there.

I found that this formula works well but it counts everything.
=MAX(FREQUENCY(IF(A2:G2=1,COLUMN(A2:G2)),IF(A2:G2<>1,COLUMN(A2:G2))))

 

[JustynaMK]

Hi tzianhao,

One solution might be to find the last empty column using the following Array formula (column K):
=MAX(--((LEN($A2:$G2)=0)*COLUMN($A2:$G2)))
Enter it with Ctrl+Shift+Enter

Then, in column L, you can sum all values that are to the right of this one last empty column:
=SUM(INDEX($A2:$G2,1,$K2+1):INDEX($A2:$G2,1,7))

Combined Array formula (Ctrl+Shift+Enter):
=SUM(INDEX($A2:$G2,1,(MAX(--((LEN($A2:$G2)=0)*COLUMN($A2:$G2))))+1):INDEX($A2:$G2,1,7))

Does it help?

 

[JustynaMK]

Or even simpler...

=COLUMNS(A2:G2)-MAX(--((LEN($A2:$G2)=0)*COLUMN($A2:$G2)))
(Ctrl+Shift+Enter)