Counting text data until change in state

[aaberndt]

Trying to count the number of BOBs or WOWs in the BOB\WOW column before there is a switch from one to the other. It doesn't matter if the first cell is a BOB or a WOW I just need a numerical count on how many there are in a row before there is a switch. In each of the BOB\WOW columns I will do this from the top of the column (count down until there is a switch) and from the bottom (count up until there is a switch) and adding them together to get the end count. I put an example in below of what the user enters and then below that I tried to illustrate what I was trying to get excel to do in counting the BOBs or WOWs.

I had thought about using VBA for loops or some version of the COUNTIFS array formula but just cant get it to work.

Thank you in advance for any help

Example Data

[/URL][/IMG]


Example Solution (trying to solve for the end count cell at end of each BOB\WOW column)

[/URL][/IMG]

 

[Eric W]

Put this formula in C18 and confirm with Control+Shift+Enter, not just Enter:

=MIN(IF(C3:C17<>C2,ROW(C3:C17)))-ROW(C2)+ROW(C17)-LOOKUP(2^99,ROW(C2:C16)/(C2:C16<>C17))

copy it to the other columns.