Formula to sum multiple sequences/ranges of values within an array?

[tokendad]

First, hello everyone I'm new here!

I have been following this forum for quite a while, and it has always been a valuable resource for finding solutions to my Excel queries. However, I now have a question that I haven't been able to find an answer to, and I'm hoping you can assist me with it.

Example.xlsx
	A	B	C
1	Location	Time	Value
2	London	Arrival	5
3	London	Departure	0
4	Underway	Noon Report	1
5	Liverpool	Arrival	6
6	Liverpool	Departure	7
7	Underway	Evening Report	9
8	Blackpool	Arrival	2
9	Blackpool	Noon Report	8
10	Blackpool	Departure	4
11	Underway	Evening Report	3
12	London	Arrival	1
13	London	Departure	2
14	Nottingham	Arrival	5
15	Nottingham	Noon Report	9
16	Nottingham	Departure	3
17	Liverpool	Arrival	6
18	Liverpool	Departure	2
19	Underway	Evening Report	3
20	Shrewsbury	Arrival	3
21	Shrewsbury	Departure	1
22	Underway	Evening Report	7
23	London	Arrival	5
24	London	Departure	3
25	Liverpool	Arrival	1
26	Liverpool	Departure	9
27	Underway	Noon Report	2
28	Birmingham	Arrival	8
Example

I have an Excel spreadsheet where I need to calculate the sum of values in column C whenever columns A and B show a sequence from "London Arrival" to "Liverpool Departure". This sequence must occur exactly in that order; any other sequence should not be considered. The intended result in the example provided would be the sum of C2:C6, C12:C18, and C23:C26, which equals 65.

I attempted to solve the problem using two helper columns. The first helper column uses the formula:

=IF(OR(AND(A2="London", B2="Arrival"), AND(A2="Liverpool", B2="Departure")), 1, 0)

The second helper column uses:

=IF(E2 + F1 = 1, 1, 0)

This setup allowed me to apply SUMIF to get the result:

=SUMIF(F:F, 1, C:C) + SUMIFS(C:C, A:A, "Liverpool", B:B, "Departure")

Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Location	Time	Value
2	London	Arrival	5		1	1		SUM	65
3	London	Departure	0		0	1
4	Underway	Noon Report	1		0	1
5	Liverpool	Arrival	6		0	1
6	Liverpool	Departure	7		1	0
7	Underway	Evening Report	9		0	0
8	Blackpool	Arrival	2		0	0
9	Blackpool	Noon Report	8		0	0
10	Blackpool	Departure	4		0	0
11	Underway	Evening Report	3		0	0
12	London	Arrival	1		1	1
13	London	Departure	2		0	1
14	Nottingham	Arrival	5		0	1
15	Nottingham	Noon Report	9		0	1
16	Nottingham	Departure	3		0	1
17	Liverpool	Arrival	6		0	1
18	Liverpool	Departure	2		1	0
19	Underway	Evening Report	3		0	0
20	Shrewsbury	Arrival	3		0	0
21	Shrewsbury	Departure	1		0	0
22	Underway	Evening Report	7		0	0
23	London	Arrival	5		1	1
24	London	Departure	3		0	1
25	Liverpool	Arrival	1		0	1
26	Liverpool	Departure	9		1	0
27	Underway	Noon Report	2		0	0
28	Birmingham	Arrival	8		0	0
Example

Cell Formulas
Range	Formula
E2:E28	E2	=IF(OR(AND(A2="London",B2="Arrival"),AND(A2="Liverpool",B2="Departure")),1,0)
F2:F28	F2	=IF(E2+F1=1,1,0)
I2	I2	=SUMIF(F:F,1,C:C)+SUMIFS(C:C,A:A,"Liverpool",B:B,"Departure")

However, this workaround has limitations. It only functions correctly when "London Arrival" appears first in column A. If "Liverpool Departure" is the initial entry, it disrupts the calculation. Additionally, I would prefer a single formula solution without needing helper columns.

I would greatly appreciate any assistance with this issue.

 

[Engberg]

Are you open to using VBA to create a custom formula, or no?

 

[Fluff]

How about

=SUM(SIGN(SCAN(0,A2:A28&"|"&B2:B28,LAMBDA(a,b,IF(b="london|arrival",1,IF(AND(b="liverpool|departure",a=1),2,IF(a=1,1,0))))))*C2:C28)