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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,959
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,132,912
Messages
5,655,914
Members
418,251
Latest member
aondrla

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