Google Sheets – Sum If Date is in Range

google sheets

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€

Best 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 ,.

Related Topic