Counting text data until change in state

aaberndt

New Member
Joined
Jan 30, 2019
Messages
1
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

OZQnueP.png
[/URL][/IMG]


Example Solution (trying to solve for the end count cell at end of each BOB\WOW column)
Zx712GC.png
[/URL][/IMG]
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,807
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,579
Messages
5,637,209
Members
416,961
Latest member
sigrid6940

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?

Disable AdBlock

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
Top