Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
share|improve this question
add comment

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

This question came from our site for computer enthusiasts and power users.

3 Answers

up vote 5 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 ;

share|improve this answer
    
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
1  
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
add comment

Use this function instead of a sumproduct

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

share|improve this answer
    
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
add comment

This worked perfectly for me in Google Spreadsheets / Google Documents / Google Docs

=(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.

Also here is the link to the SUMIF Google Help Page https://support.google.com/drive/answer/3093583

I hope that's all clear.

share|improve this answer
add comment

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.