How to I sum multiple range of values within an array?

Question

Excel file:

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.

Workaround:

I attempted to solve the problem using two helper columns. The first column used the formula =IF(OR(AND(A2="London", B2="Arrival"), AND(A2="Liverpool", B2="Departure")), 1, 0), while the second used =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").

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.

Answer 1

Attempting to REDUCE after SCANning JvdV's :)

=LAMBDA(data,
    LET(
        indices, SEQUENCE(ROWS(data)),
        sum_, REDUCE(
            HSTACK(FALSE, 0),
            indices,
            LAMBDA(acc, cur,
                LET(
                    is_adding, INDEX(acc, , 1),
                    prev_sum, INDEX(acc, , 2),
                    loc_, INDEX(data, cur, 1),
                    time_, INDEX(data, cur, 2),
                    value_, INDEX(data, cur, 3),
                    state_and_sum, IFS(
                        AND(
                            loc_ = "London",
                            time_ = "Arrival",
                            NOT(is_adding)
                        ),
                        HSTACK(TRUE, prev_sum + value_),
                        AND(
                            is_adding,
                            loc_ = "Liverpool",
                            time_ = "Departure"
                        ),
                        HSTACK(NOT(is_adding), prev_sum + value_),
                        is_adding,
                        HSTACK(is_adding, prev_sum + value_),
                        NOT(is_adding),
                        HSTACK(is_adding, prev_sum)
                    ),
                    state_and_sum
                )
            )
        ),
        HSTACK("Sum", INDEX(sum_, , 2))
    )
)(A2:C28)

Answer 2

Usually not a fan of concatenating different columns but I can't see the harm on this particular usecase:

Formula in E2:

=SUM(FILTER(C2:C28,SCAN(0,A2:A28&"|"&B2:B28,LAMBDA(x,y,IF(y="London|Arrival",1,IF(AND(x=1,y="Liverpool|Departure"),2,IF(x=2,0,x)))))))