Getting a sum of cells (bottom to top) in Google Sheets until a specific value is matched

Question

I'd like to sum up values of specific cells in a column (In this case column A) from bottom to top, in order to get the latest added values, until they match a specific value (if greater than zero) from another cell (in this case 7 as noted in Cell A1). If they match that value, I'd like to have a sum of the cells next to column A.

In the example below, I'd like to start summing up from cell A7 upwards, since the cells below are empty. So it keeps going up one row until the sum equals a predefined value (in this case: 7 as noted in Cell A1).

So if A7, A6 and A5 match 7, I'd like to see the sum the sum of cells B7, B6 and B5. In the example below, the sum of values in the B column should thus be 8

Example:

Row  |  Column A   |  Column B   |  Column C
1    |  7          |             |  Total: 8
2    |             |             |
3    |  8          |  1          |
4    |  3          |  2          |
5    |  4          |  3          |
6    |  2          |  3          |
7    |  1          |  2          |
9    |  [empty]    |  [empty]    |
10   |  [empty]    |  [empty]    |
11   |  [empty]    |  [empty]    |

I hope the above is clear, and I am very curious if this can be done.

Answer 1

Yes, this can be done.

Following I will show an approach that use the running total formula posted by AdamL but first, it prepares the input data to be used by the referred formula:

1. First sort the input range in inverse order
   • C1: =SORT({{A3:B7},ROW(A3:A7)},3,FALSE)
2. Calculate the running sum for the first (key) column
   • F1: =ArrayFormula(SUMIF(ROW(C3:C7),"<="&ROW(C3:C7),C3:C7))
3. Calculate the running sum for the second (value) column
   • G1: =ArrayFormula(SUMIF(ROW(D3:D7),"<="&ROW(D3:D7),D3:D7))
4. Find and return the output
   • H1: =VLOOKUP(A1,F3:G7,2)

Following there is another approach. I made a slight change to the AdamL's formula (replace <= by >):

1. Calculate the running sum for the first (key) column
   • C1: =ArrayFormula(SUMIF(ROW(A3:A7),">"&ROW(A3:A7),A3:A7))
2. Calculate the running sum for the second (value) column
   • D1: =ArrayFormula(SUMIF(ROW(B3:B7),">"&ROW(B3:B7),B3:B7))
3. Find and return the output
   • E1: =VLOOKUP(A1,A3:B7,2,FALSE)

Demo file