Date based sum in Excel / Google Docs spreadsheets

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.

Replay

The code

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

should work in both Excel and Google Spreadsheets.

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.

Category: microsoft excel Time: 2008-08-29 Views: 2

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.129 (s). 12 q(s)