8

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
| improve this question | |
8

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 ;

| 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
2

Use this function instead of a sumproduct

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

| 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
0

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.

| improve this answer | |
0
 ">="&B:1 

=Sumif(U13:X13,ʺ<=ʺ&B4,Y13:AB13)

Checks the valve of u13:x13 (which are dates) against todays date, then sums the values (Dividends in £) thus providing the current amount of dividend paid todate!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.