Google-sheets – LOOKUP-like behaviour adding up rows down to the found one

google sheetsgoogle-sheets-datesgoogle-sheets-query

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.

Best Answer

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

Related Topic