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.

  • What should happen if the sums never match the value 7? – user79865 Jul 13 '16 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 '16 at 20:39
  • Really? What if A1 had the value of 5 in your example? – user79865 Jul 13 '16 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 '16 at 20:47
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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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