Is there a process in Excel that can automate reading through a column containing TRUE/FALSE values and can add numbers incrementally in a new column?

I have a very large spreadsheet containing over 50,000 rows. They are records of events from a camera trap. I have a column called "NewEvent" which contains only TRUE or FALSE values, which was based on whether the camera trap was triggered as a new event. However now I need to number the events in the order that they happened and record these values in a new column. For example, the very first TRUE would be marked as event 1 in the new column, and all FALSE values following it would also still be part of event 1, until there is another TRUE which would then be event 2 and so on. I started doing this manually but realized it will take me a considerable amount of time. Is there any way to automate this using VBA or macro etc in Excel? Please see the example below:

I tried experimenting with the code editor in Macro but I am very new to this and found it a little complex for me. Thank you so much!

UPDATE: This thread turned into a bit of Code Golf. The shortest answer appears to be:

=N(B1)+A2

ORIGINAL ANSWER:

Using COUNTIFS with a mixed range reference. May bog down the worksheet if you have tens of thousands of rows!

=COUNTIFS(A$2:A2,TRUE)

Another (more performant) option:

=IF(A2,SUM(B1,1),B1)

Yet another option (credit to Mayukh Bhattacharya)

=IF(A2,N(B1)+1,B1)

Other than the solution provided by BigBen Sir, you can use <kbd>SCAN( )</kbd> to spill.

• Formula used in cell B2

=SCAN(0,--A2:A13,LAMBDA(x,y,IF(y,x+1,x)))

Edit: For the Spill Array!

• Formula used in cell B1

=SCAN(0,--A2:A13,LAMBDA(x,y,(y+N(x))))

Another alternative approach. Without <kbd>IF( )</kbd> and using only <kbd>N( )</kbd>

• Formula used in cell B1

=N(A2)+N(B1)

OfCourse the formula needs to fill down!!

The above formula can be made more shorter as proposed by @BigBen Sir.

=A2+N(B1)

A mandatory caveat : Formulas shown above all assumes, that the range starts with TRUE, if it isn't then one could use as below:

• Formula used in cell B1

=(A2+N(B1))+1

For fun:

Formula in B2:

=MAP(A2:A13,LAMBDA(x,SUM(--A2:x)))

Or, with SCAN():

=SCAN(0,A2:A13,LAMBDA(x,y,x+y))

What about this old-fashioned trick:

Imagine you have your "TRUE"/"FALSE" values in column "A" and you want your counter in column "B", then you start column "B" with 1 (as you always start with a TRUE"), and the following cell in column "B" gets following formula:

=IF(A2=TRUE,B1+1,B1)

This checks the value in the "A" column. In case of TRUE, an increase (B1+1) is done, otherwise the normal value (B1) is used.

The result looks as follows: