What this does is, if there are no Red entries, it means that every entry is a consecutive Green, so it just counts the Green entries associated with PM. If there are red entries, C4:C15="Red" creates an array of values that are either TRUE or FALSE. It is TRUE if the value is "Red" and FALSE otherwise. By dividing 1 by this array, it creates a new array (every entry is either 1 or #DIV/0! where it is 1 if it was TRUE and the error otherwise). The LOOKUP function is looking for 2, but it will take the last entry that is closest to 2 (so it takes the last 1). This corresponds to an array of numbers (we have 12 entries in C4:C15, so we need an array of numbers 1:12 corresponding to the number of rows to skip). The INDEX function then takes the array C4:C15 and finds the last entry containing "Red" and starts the array. This goes to C15. Then, it counts the number of times Green appears in that modified array with "PM" in column B. This tells you the number of consecutive Greens.
Bookmarks