count until another column changes then reset count

[guhl_yarden]

Hi All,

Could please someone help me with some formula? It drives me crazy since days/weeks.
I would like to do the following but dont even have an idea how to describe it :D

Count "A" until there is "B". Stop countung "A" but start count "B". If there is "A" again start over but add 1 to the last "A" value Hope its clear :D
Maybe this picture helps more than 1000 words.

Im using Excel 2016 but have uploaded on google drive as well in the case the picture gets lost in the ether:
https://docs.google.com/spreadsheets/d/1ZCiNsD6bYOnp74HUy7V1ZLZRz9QZl_nt3l59PO83czs/edit?usp=sharing

Thanks in advance everyone

 

[wideboydixon]

If "A" always occurs first then:

Book1
	A	B	C	D
3			4	3
4
5	A		1
6	A		1
7
8	A		1
9	A		1
10
11
12		B		1
13		B		1
14		B		1
15
16		B		1
17
18	A		2
19	A		2
20	A		2
21	A		2
22		B		2
23		B		2
24
25	A		3
26	A		3
27		B		3
28
29
30
31
32		B		3
33
34
35	A		4
Sheet1

Cell Formulas
Range	Formula
C3	=MAX(C$5:C$35)
C5	=IF($A5="A",MAX($D$4:$D4)+1,"")
D3	=MAX(D$5:D$35)
D5	=IF($B5="B",MAX($C$4:$C4),"")

WBD

 

[guhl_yarden]

Thank you WBD! You made my day :D Works like a charm )) /crying in happines