I have a bunch of rows with a date and a dollar amount (expenses).

I want to produce a list of the days of the month and what the balance of the expenses is. So, for example the 5th entry in the list would be 8/5/2008 and the sum of all the expenses that occurred on or before 8/5/2008. Approximately this is =sumif(D4:D30-A5,">0",E4:E30) but of course that doesn't work (where the source data is dates in D4:D30 and the expenses are in E4:E30).


  • source data can't be sorted for various reasons.
  • must work in google spreadsheets, which is a fairly complete subset of excel's functions.

The code


should work in both Excel and Google Spreadsheets.

  • Is 6 years a record for accepting an answer? Should have done this 20 min after you submitted the answer. Better late than never. – alumb Jan 8 '15 at 18:36

Assume your data are in D and E:

In column A, all dates of the month. In column B, all dates of the month converted to numbers (It's something like days since 1 Jan 1970 as a default.)

In Column F, all dates in Column D converted to numbers.

Then cells in in columns G:AM (or so), this formula: If (F1>B$1,0,E1) For each column, B1 goes one higher, so it's B1, b2, etc.

In cells in columns C(1-31) sum columns G-AM).

Then column C consists of 31 numbers corresponding to the 31 dates in column A.

  • Super User compiles questions and answers for reference. To support that, answers should be just definitive answers. If you need clarification before answering,that should be done with comments (which it shouldn't take you long to acquire enough rep for). If you need to qualify your answer as being based on certain assumptions, that's fine. This question has a bounty so I won't change anything in your answer. At some point, please edit your answer to remove the first paragraph, which wouldn't be an appropriate part of an answer when people come looking at it later. Good luck. – fixer1234 Dec 19 '14 at 1:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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