Find Consecutive months without counting empty cell in the middle

[tzianhao]

	A	B	C	D	E	F	G	H
1	1803M	1804M	1805M	1806M	1807M	1808M	1809M	Remarks
2		1		1	1		1	First Month
3		1		1		1	1	2 Consecutive months
4	1	1			1	1	1	3 Consecutive months
5				1	1	1	1	4 Consecutive months
6	1		1	1	1	1	1	5 Consecutive months

<tbody>
</tbody>

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)