Based on what you've described, something like this should give you the sum, but you should consider flattening your table to avoid the difficulties in determining where the range to sum begins and ends.
MrExcel_20220603.xlsx |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
1 | | | | | | | | |
---|
2 | A1 | X1 | | 1 | | | | |
---|
3 | | | | 2 | | | colA | A2 |
---|
4 | | | | 3 | | | colB | X2 |
---|
5 | | | | 4 | | | sum of D | 26 |
---|
6 | A2 | X2 | | 5 | | | | |
---|
7 | | | | 6 | | | | |
---|
8 | | | | 7 | | | | |
---|
9 | | | | 8 | | | | |
---|
10 | A3 | X3 | | 9 | | | | |
---|
11 | | | | 10 | | | | |
---|
12 | | | | 11 | | | | |
---|
13 | something | something | | 12 | | | | |
---|
14 | | | | 13 | | | | |
---|
15 | | | | 14 | | | | |
---|
16 | | | | 15 | | | | |
---|
17 | x | x | | | | | | |
---|
|
---|
If you flattened the table so that it appears like this, then the summation can be done with a much simpler approach:
To flatten the table, you could use a VBA script to fill in the blanks, Power Query, or even the approach described in the 1st method here (which is what I used).
Watch Video - Fill Down in Excel
trumpexcel.com