# LOOKUP-like behaviour adding up rows down to the found one

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.

## 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:

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

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

You can also use the following QUERY formula:

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

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)
``````