2

I have a table with dates in column A and values in column B.

     A           B
1    2020-12-30  1
2    2020-12-31  2
3    2021-01-04  4

Given a DATE, I can LOOKUP the last value before (including) that date:

=lookup(DATE,A1:A,B1:B)

For example, it returns 2 for both DATE=2021-01-01 and DATE=2020-12-31. How can I get the sum of all values down to (including) the value found by lookup? That is, I would like to receive 3 in the above example (add up rows 1 and 2).

The rows are sorted by date.

2 Answers 2

1

Explanation:

The logic of the following formula is:

  • find the position at which the selected date occurs in column A:

    match(date(2021,1,1),A1:A)

  • convert it to text and concatenate it with B1:B:

    "B1:B"&to_text(match(date(2021,1,1),A1:A))

    to get the range reference in a string format.

  • use indirect to convert the string reference to an actual reference:

    indirect("B1:B"&to_text(match(date(2021,1,1),A1:A)))

    and finally get the sum of this expression.

Solution:

How about this:

=sum(indirect("B1:B"&to_text(match(date(2021,1,1),A1:A))))

example

1
  • It is complex! Thank you!
    – texnic
    Jan 5, 2021 at 14:33
1

You can also use the following QUERY formula:

=QUERY(A1:B,"select sum(B) where A<=date'2021-01-01' ",0)

enter image description here

To take it a step further, you can also take the date out of the formula and use

=QUERY(A1:B,"select sum(B) where A<=date'"&TEXT(C1,"yyyy-mm-dd")&"' ",0)

enter image description here

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.