I have a complex year-long schedule for 25 people in google sheets and I need to quantify how often each individual has to work multiple nights in a row over the course of the entire year. I need to bin these into 2 nights in a row, 3 in a row, etc. Here is a simplified view of 1 week, in which I need to count consecutive occurrences of "Back Up IV Call". I have found some similar tasks using query function and vlookup but am having trouble adapting this to my data. Is anyone able to give me a hand with this?

Get the result in a table

Rename your input sheet to Sheet1
And In another sheet, paste this formula.

=ArrayFormula(IF(Sheet1!B3:B="",, { Sheet1!B3:B,SPLIT(Sheet1!A3:A, ", ", 0)}))

Explanation

01 - REGEXMATCH(B3:3, "Back Up IV Call" )
To get array of TRUE / FALSE

02 - IF(B3:3="",,
IF B3:3 cells is empty "" then do nothing ,, IF not excute the REGEXMATCH formula.

03 - TEXTJOIN(",",1,IF(B3:3="",, REGEXMATCH(B3:3, "Back Up IV Call" )))
To TEXTJOIN the TRUE / FALSE array with ",".

04 - REGEXREPLACE(TEXTJOIN(...)
To Replace "FALSE," with a 0.

05 - SPLIT(REGEXREPLACE(TEXTJOIN(...)
To Split the jointed arrar "cell" with 0 set as a dilimiter.

06 - FLATTEN(...)
To Flatten the streaks in one column for example TRUE | TRUE,TRUE in one column

TRUE
-----------
TRUE,TRUE