Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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€
share|improve this question
    
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 at 11:33

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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