Count the number of instances of a word since another word

Cubist

Well-known Member

Joined

Oct 5, 2023

Messages

1,175

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Thursday at 11:02 AM

• #2

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)

 

Upvote 0

C

comonand

New Member

Joined

Feb 7, 2022

Messages

14

Office Version

1. 365

Platform

1. Windows

• Thursday at 12:52 PM

• #3

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.

 

Upvote 0

Cubist

Well-known Member

Joined

Oct 5, 2023

Messages

1,175

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Thursday at 1:12 PM

• #4

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

 

Upvote 0

C

comonand

New Member

Joined

Feb 7, 2022

Messages

14

Office Version

1. 365

Platform

1. Windows

• Thursday at 1:16 PM

• #5

Cubist said:

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

Click to expand...

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.

 

Upvote 0

Cubist

Well-known Member

Joined

Oct 5, 2023

Messages

1,175

Office Version

1. 365

Platform

1. Windows
2. MacOS

• Thursday at 1:28 PM

• #6

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")))

 

Upvote 0

Solution

C

comonand

New Member

Joined

Feb 7, 2022

Messages

14

Office Version

1. 365

Platform

1. Windows

• Thursday at 1:43 PM

• #7

Perfect, thanks.

 

Upvote 0