Excel

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.