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? Commented Feb 19, 2014 at 11:33

1 Answer 1


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.