How to sum values in a range of dates in Google Docs Spreadsheets?

Question

I have a Google Docs spreadsheet in the format below — here's an example.

I'd like to sum values in column B in a range of dates in column A.

Example:

Sum values between 01-feb-12, and 29-feb-12.

In Excel, I used the following formula:

=SUMPRODUCT(N(A1:A1000>=A35),N(A1:A1000<=A36),B1:B1000)

where:

fixitagain · Accepted Answer · 2012-02-28 08:25:59Z

up vote 4 down vote accepted

This works, see F35 in your sheet:

SUMIF(E2:E33;">="&E35;B2:B33)-SUMIF(E2:E33;"<="&E36;B2:B33)

column E is done by calculating a value for the dates to be able to compare them (=VALUE(E1))

Note that separating the args for some reasons is done with ;

answered Feb 28 '12 at 8:25

fixitagain
1762

	It works! Thank you very much! But I don't need VALUE() function. The problem was with ; to separate the arguments. I used =sumif(A2:A33;">"&A36;B2:B33) and it worked perfectly. – Victor Santos Feb 28 '12 at 12:17
	just for clarification: yes it works because it use then alphabetical order on string, but you may have issues with different years... – fixitagain Feb 28 '12 at 13:41
	@fixitagain ah, I was wondering why I couldn't get my equation to work and I had wrongly assumed it was because of the formatting of the dates. Good info and +1 – Raystafarian Feb 28 '12 at 14:30
	I had to use this formula: SUM(B2:B33)-sumif(A2:A33;">="&A36;B2:B33)-sumif(A2:A33;"<="&A35;B2:B33) // Now everything is ok. Thank you again. – Victor Santos Feb 28 '12 at 17:34
	if you want to have alphabetical order coincidating with chronological, use the following format: `2012-02-29` ie `yyyy-mm-dd` – fixitagain Feb 29 '12 at 14:34

Raystafarian · Answer 2 · 2012-02-28 14:31:22Z

up vote 1 down vote

Use this function instead of a sumproduct

=sumif(A1:A1000;">="&A35;B1:B1000)-sumif(A1:A1000;"<"&A36;B1:B1000)

edited Feb 28 '12 at 14:31

answered Feb 24 '12 at 19:35

Raystafarian
53627

	Not work. :( I put the link to a sample worksheet. Thank you very much. – Victor Santos Feb 24 '12 at 20:16
	switch the signs. I'll edit my equation and try again – Raystafarian Feb 24 '12 at 20:22

migrated from superuser.com Feb 28 '12 at 2:47