# How to sum ​​values ​​in a range of dates

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

• Column A → Dates
• Column B → Numbers

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:

• A35 = Start Date
• A36 = Last date

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 `;`

• 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. Feb 28, 2012 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... Feb 28, 2012 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 Feb 28, 2012 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. Feb 28, 2012 at 17:34
• if you want to have alphabetical order coincidating with chronological, use the following format: `2012-02-29` ie `yyyy-mm-dd` Feb 29, 2012 at 14:34

Use this function instead of a `sumproduct`

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

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

``````=(SUMIF(A:A,">= 01/12/13", E:E))-(SUMIF(A:A,">= 31/12/13", E:E))
``````

If you want to reference cells in place of writing the date '01/12/13' in the formula itself, use the logical operation in quotes, then an &(ampersand) and the cell reference: ">="&B:1

``````=(SUMIF(A:A,">="&B:1, E:E))-(SUMIF(A:A,">="&B:2, E:E))
``````

Also the > (greater than) or < (less than) sign must come before the = (equals) sign.

The above formula works like so: all values after 01/01/13 = 350. All values after 31/01/13 = 250. Therefore values between 01/01/13 and 31/01/13 = 350 - 250 = 100.

`````` ">="&B:1