Count until change in value, then reset, and sum, please -

A

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
102
Hello -

Thanks for any and all help on this!

Basically, I need some help please on how many times a value in BS occurs in a sequence. And then, if the value occurs again, to know the sum of the new sequence.


(To start, for clarity: COL BS in the example below is being pulled from COL AG in my real worksheet. SO BS = AG in the code example, thx...)

OK -- I am trying to count the number of occurrences that repeat in COL BS, until the repeated value changes.

At the point that it changes, the count (of the new value) should start from 1.

If the next change in value is equal to a previous value, the count should start (again) from 1.

In the example below, you can see that for one thing:

A) BR3 should be the start of the count (1), as BS3 = BS2. I don't know why that is incorrect, please...and:

B) that the formula DOES count until the change in value, but when it changes again, back to a previous value, it picks up counting where it left off. I would like it to reset at each value change.

(Ideally, I do not need to see the individual count, but only the sum, in COL BR. But is not a problem if it is difficult to arrange.)


Thank you all for your insight - much appreciated!


Excel 2016 (Mac) 32 bit
BRBS

<tbody>
[TD="align: center"]2[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]down[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]FALSE[/TD]
[TD="align: center"]down[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]down[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]down[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]down[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]down[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]down[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]down[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]down[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]26[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]27[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]up[/TD]

[TD="align: center"]29[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]up[/TD]

</tbody>
Main​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]BR2[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]A2=A1,COUNTIF(AG$2:AG2,AG2)[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]BS2[/TH]
[TD="align: left"]=AG2[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]BR3[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]A3=A2,COUNTIF(AG$2:AG3,AG3)[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]BS3[/TH]
[TD="align: left"]=AG3[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]BR4[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]A4=A3,COUNTIF(AG$2:AG4,AG4)[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]BS4[/TH]
[TD="align: left"]=AG4[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]BR5[/TH]
[TD="align: left"]=IF([COLOR=rgb(255]A5=A4,COUNTIF(AG$2:AG5,AG5)[/COLOR])[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]BS5[/TH]
[TD="align: left"]=AG5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Like this, copied down?

Excel Workbook
BRBS
1
21down
32down
43down
54down
65down
76down
87down
98down
101up
112up
123up
134up
145up
156up
167up
178up
189up
1910up
2011up
2112up
2213up
231down
241up
252up
263up
274up
285up
296up
Count
 
Upvote 0
Peter -

Thank you so much! That worked just great -

I very much appreciate your help...


Now, a follow on question please:

Is it possible to have a column BT, that will show the highest result from the string of occurrences before it starts from 1 again?

so:

BT9 = 8
BT22 = 13
BT23 = 1
BT29 = 6


Again, thank so much for your insight - it is much appreciated!
 
Upvote 0
aiki100 said:
Peter -

Thank you so much! That worked just great -

I very much appreciate your help...
Click to expand...
You are very welcome.


aiki100 said:
Now, a follow on question please:

Is it possible to have a column BT, that will show the highest result from the string of occurrences before it starts from 1 again?

so:

BT9 = 8
BT22 = 13
BT23 = 1
BT29 = 6
Click to expand...
Sure ..

Excel Workbook
BRBSBT
1
21down 
32down
43down
54down
65down
76down
87down
98down8
101up
112up
123up
134up
145up
156up
167up
178up
189up
1910up
2011up
2112up
2213up13
231down1
241up
252up
263up
274up
285up
296up6
Count
 
Upvote 0
Peter -

Outstanding, thank you!

I do very much appreciate the assist and now see how that was done.

Hope you have a great day where you are -

Thx
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
How to sum values in column D until a blank cell is reached and if values in column C equals "WP" or "BS"
Replies
9
Views
2K
Peter_SSs
Peter_SSs
R
  • Question Question
Graph dynamic range and lines don't work properly when one or more values are not present; legend
Replies
6
Views
1K
Rnkhch
R
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
1K
AndyEd
A
ibmy
  • Question Question
Sequence of Up->Down->Up-> repeat until....
Replies
2
Views
602
ibmy
ibmy
M
  • Question Question
Replacing OFFSET to sum up to max value from specific cell
Replies
3
Views
1K
JamesCanale
JamesCanale

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

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
Back
Top