# Sum if date is in range

I have a table of random dates `A` and a sum for each date `B`.

The sum is over all the amounts in `D`, where the date in `C` is before the date of `A`. So `B3` is `D1+D2+D3+D4+D5+D6` because the date in `A3` is past the date in `C6`.

How could I do this with a formula?

``````  A              B      C          D
---------------------------------------
2.3.2013         45     1.3.2013   45€
6.4.2013         90     1.4.2013   45€
5.8.2013         270    1.5.2013   45€
1.1.2014         315    1.6.2013   45€
3.1.2014         315    1.7.2013   45€
5.2.2014         315    1.8.2013   45€
9.2.2014         315    1.9.2013   45€
``````
-
Are the dates in column `A` sorted, and what date format are you using? `DD.MM.YYYY` or `MM.DD.YYYY`? – Vidar S. Ramdal Feb 19 '14 at 11:33

## 1 Answer

Try this formula for `B1`, copy-paste (or drag across) to other cells in column `B`:

``````=SUM(FILTER(D\$1:D; A1 > C\$1:C))
``````

The `FILTER` includes only values from column `D` which have a corresponding value in column `C` which is lower than `A1`. So for row 1, this would mean `=SUM(FILTER(D\$1:D; 2013-03-02 > C\$1:C))`.

When copying this formula to the other rows, `A1` should be automatically replaced with `Ax`, where `X` is the row number.

I have created an example spreadsheet to demonstrate.

Note that I use `;` in the formula. Depending on your locale settings for Google Spreadsheets, you might have to replace `;` with `,`.

-