# Counting text data until change in state

#### aaberndt

##### New Member
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]

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.

Replies
8
Views
567
Replies
8
Views
304
Replies
1
Views
70
Replies
3
Views
340
Replies
16
Views
176

1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back