First, hello everyone I'm new here! ![Smile :) :)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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. ![Wink ;) ;)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
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:
The second helper column uses:
This setup allowed me to apply SUMIF to get the result:
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.
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.
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:
Excel Formula:
=IF(OR(AND(A2="London", B2="Arrival"), AND(A2="Liverpool", B2="Departure")), 1, 0)
Excel Formula:
=IF(E2 + F1 = 1, 1, 0)
Excel Formula:
=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.