How to sum up values between 2 dates in Excel 2007

Question

Lets say I would like to sum up values from January to March. Below is an example

╔═══════════╦════════════╗
║ Column A  ║ Column B   ║
╠═══════════╬════════════╣
║ 1/30/2011 ║ 1          ║
║ 1/25/2011 ║ 1          ║
║ 3/30/2011 ║ 1          ║
║ 3/25/2011 ║ 1          ║
║ 5/13/2011 ║ 1          ║
╚═══════════╩════════════╝

I did some research and found I can use the SUMIFS function

=SUMIFS(B1:B5,A1:A5,">="&DATE(YEAR(2011),MONTH(1),DAY(1)),A1:A5,"<="&DATE(YEAR(2011),MONTH(4),DAY(1)))

But for some reason instead of returning 4 it returns 0. I would really appreciate if someone could figure out why.

Thank you

bombnomnom · Accepted Answer · 2011-10-23 03:14:15Z

2

I don't think Year/Month/Day do what you're expecting, see:

http://www.techonthenet.com/excel/formulas/year.php

They return the year value, month value, and day value of their argument. Try entering

=YEAR(2011)

and compare it to

=YEAR("1/30/2011")

That said, you can get what you want by just putting the dates in the quotes

=SUMIFS(B1:B5,A1:A5,">=2011-01-01",A1:A5,"<=2011-04-01")

produces 4 in my Excel.

edited Oct 23, 2011 at 3:14

answered Oct 23, 2011 at 3:09

bombnomnom

613 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Igor Over a year ago

you are correct, I had the Year/dates functions wrong. Your example is the answer and it helped me out greatly. Thank you. I still needed to create a generic spreadsheet for the current year so I went with this function. =SUMIFS(B1:B5,A1:A5,">="&DATE(YEAR(TODAY()),1,1),A1:A5,"<="&DATE(YEAR(TODAY()),4,31))

Collectives™ on Stack Overflow

How to sum up values between 2 dates in Excel 2007

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related