A formula that counts last consecutive filled cells

[Oi_Oi_Olivia]

Hi all,
This is a brain twister for me but I've looked around and you've helped on similar things so I hope you can help me.
My data looks like this
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]7/1[/TD]
[TD]7/2[/TD]
[TD]7/3[/TD]
[TD]7/4[/TD]
[TD]7/5[/TD]
[TD]7/6[/TD]
[TD]7/7[/TD]
[TD]Total[/TD]
[TD]Last consecutive total[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

Can you help with a formula for the last column?
For context this is the length of the current period of time in which an event has occurred daily.
TIA,
Olviia

 

[pgc01]

Hi Olivia
Welcome to the board

Try in I2:

=1/LOOKUP(2,1/FREQUENCY(IF(A2:G2<>"",COLUMN(A2:G2)),IF(A2:G2="",COLUMN(A2:G2))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Copy down

 

[Oi_Oi_Olivia]

Thanks!! That's flippin' magic!
Worked like a charm.

 

[pgc01]

You're welcome. Thanks for the feedback.