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

[aiki100]

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

	BR	BS
2	FALSE	down
3	FALSE	down
4	3	down
5	4	down
6	5	down
7	6	down
8	7	down
9	8	down
10	1	up
11	2	up
12	3	up
13	4	up
14	5	up
15	6	up
16	7	up
17	8	up
18	9	up
19	10	up
20	11	up
21	12	up
22	13	up
23	9	down
24	14	up
25	15	up
26	16	up
27	17	up
28	18	up
29	19	up

<tbody>
</tbody>

Main​

Worksheet Formulas Cell Formula BR2 =IF([COLOR=rgb(255]A2=A1,COUNTIF(AG$2:AG2,AG2)[/COLOR]) BS2 =AG2 BR3 =IF([COLOR=rgb(255]A3=A2,COUNTIF(AG$2:AG3,AG3)[/COLOR]) BS3 =AG3 BR4 =IF([COLOR=rgb(255]A4=A3,COUNTIF(AG$2:AG4,AG4)[/COLOR]) BS4 =AG4 BR5 =IF([COLOR=rgb(255]A5=A4,COUNTIF(AG$2:AG5,AG5)[/COLOR]) BS5 =AG5 <tbody> </tbody>

<tbody>
</tbody>

 

[Peter_SSs]

Like this, copied down?

Excel Workbook
	BR	BS
1
2	1	down
3	2	down
4	3	down
5	4	down
6	5	down
7	6	down
8	7	down
9	8	down
10	1	up
11	2	up
12	3	up
13	4	up
14	5	up
15	6	up
16	7	up
17	8	up
18	9	up
19	10	up
20	11	up
21	12	up
22	13	up
23	1	down
24	1	up
25	2	up
26	3	up
27	4	up
28	5	up
29	6	up
Count

 

[aiki100]

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!

 

[Peter_SSs]

Peter -

Thank you so much! That worked just great -

I very much appreciate your help...

You are very welcome.

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

Sure ..

Excel Workbook
	BR	BS	BT
1
2	1	down
3	2	down
4	3	down
5	4	down
6	5	down
7	6	down
8	7	down
9	8	down	8
10	1	up
11	2	up
12	3	up
13	4	up
14	5	up
15	6	up
16	7	up
17	8	up
18	9	up
19	10	up
20	11	up
21	12	up
22	13	up	13
23	1	down	1
24	1	up
25	2	up
26	3	up
27	4	up
28	5	up
29	6	up	6
Count

 

[aiki100]

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

 

[Peter_SSs]

Cheers. Same to you.