Count the number of instances of a word since another word

[comonand]

I'm not sure what function to use for this, i have a table like below, i would like to count the number of occurences of the word "Charge" since the word "Break in" for cell number 69. Feels like xlookup and countif but not sure. Help please!

batteries.xlsx
	A	B	C
1	Cell No	Date	Action
2	68	03/04/2024	Break in
3	69	03/04/2024	Break in
4	69	09/04/2024	In Use
5	40	09/04/2024	In Use
6	47	27/04/2024	In Use
7	65	27/04/2024	In Use
8	69	27/04/2024	Charge
9	69	17/05/2024	In Use
10	51	17/05/2024	In Use
11	57	17/05/2024	In Use
12	69	17/05/2024	Charge
13	9	18/05/2024	Charge
14	8	18/05/2024	Charge
15	1	23/05/2024	In Use
16	10	23/05/2024	In Use
17	47	23/05/2024	Charge
18	65	23/05/2024	Charge
Sheet6

 

[Cubist]

You can use COUNTIFS to count multiple criteria.

Book1
	A	B	C	D	E
1	Cell No	Date	Action
2	68	3/4/2024	Break in		1
3	69	3/4/2024	Break in
4	69	9/4/2024	In Use
5	40	9/4/2024	In Use
6	47	27/4/2024	In Use
7	65	27/4/2024	In Use
8	69	27/4/2024	Charge
9	69	17/5/2024	In Use
10	51	17/5/2024	In Use
11	57	17/5/2024	In Use
12	69	17/5/2024	Charge
13	9	18/5/2024	Charge
14	8	18/5/2024	Charge
15	1	23/5/2024	In Use
16	10	23/5/2024	In Use
17	47	23/5/2024	Charge
18	65	23/5/2024	Charge
Sheet6

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS(C2:C18,"Break in",A2:A18,69)

 

[comonand]

Thanks for the reply, i want to count the number of times "charge" appears after "break in".

So the list is longer than that, thats just a section, so it will find the last occurrence of "break in" then use that cell to the end of the table to create a range and use a countif to count the number.

 

[Cubist]

Are there multiple instances of "Break in"?
Are all "Charges" always occur after "Break in"?

 

[comonand]

Are there multiple instances of "Break in"?
Are all "Charges" always occur after "Break in"?

Yes multiple instances of "break in" I only want the last one

No "charge" could appear before the last break in, I only want to count the ones after.

 

[Cubist]

Give this a try:

Book2
	A	B	C	D	E	F
1	Cell No	Date	Action		Cell	69
2	68	45385	Break in			4
3	69	45385	Break in
4	69	45391	In Use
5	40	45391	In Use
6	47	45409	In Use
7	65	45409	In Use
8	69	45409	Charge
9	69	45429	In Use
10	51	45429	In Use
11	57	45429	In Use
12	69	45429	Break in
13	9	45430	Charge
14	8	45430	Charge
15	1	45435	In Use
16	10	45435	In Use
17	47	45435	Charge
18	65	45435	Charge
19	69	45429	Charge
20	69	45429	Charge
21	69	45429	Charge
22	69	45429	Charge
Sheet2

Cell Formulas
Range	Formula
F2	F2	=LET(f,FILTER(C2:C22,A2:A22=F1),x,XMATCH("Break In",f,,-1),SUM(--(DROP(f,x)="Charge")))

 

[comonand]

Perfect, thanks.