Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Notes

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

migrated from stackoverflow.com Nov 1 '10 at 12:37

This question came from our site for professional and enthusiast programmers.

2 Answers 2

The code

=SUMIF(D$2:D$30,"<="&A5,E$2:E$30)

should work in both Excel and Google Spreadsheets.

share|improve this answer

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.

share|improve this answer
    
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 at 1:31

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.