Finding frequency of multiple negative period

Hi,

I've been trying to build a table that calculates frequency and average amount lost per period of losing streak for multiple negative periods.

If you have data with 4 consecutive negative values, the frequency under period 4 should count as one whilst the rest should be zero.

Frequency Consecutive Negative Period
0 1
0 2
0 3
1 4

For now, I'm only able to do it via setting a 1 or 0 condition for each losing period, but it is very manual if I have to do up to 30 periods.

Is there a way with other formulas? Or through frequency?

I've attached the file I'm working on. Appreciate if I can get some help here.

finding the frequency of multiple consecutive negative periods.xlsx

Thanks a lot guys! working well!

Thanks nflsales, i've marked it solved. Just a quick one, could you walk me through the logic used in cell E6?

Don't really understand how it works to derive the value of 14. I broke down the formula to each part but {(countifs(c2:c270,c2:c270)} gives me a value of 3, and {(c2:c270<0)=2} gives me FALSE.

Thanks a lot!