5

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.

4
  • What should happen if the sums never match the value 7?
    – user79865
    Jul 13, 2016 at 15:41
  • If the cell A1 contains a value greater than zero there must be a sum of one or more cells that equals the value.
    – Allard
    Jul 13, 2016 at 20:39
  • Really? What if A1 had the value of 5 in your example?
    – user79865
    Jul 13, 2016 at 20:45
  • Then, starting a count from the last row in column A containing a number upwards will eventually add up to 5. This has to do with the fact that the value in cell A1 is a sum of the last X cells in column A
    – Allard
    Jul 13, 2016 at 20:47

1 Answer 1

2

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

1
  • Works like a charm, thanks for the provided solution!!
    – Allard
    Jul 27, 2016 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.