# Sum For a Date Range in Excel

First, some news about the Office 365 launch, and then a tip for summing values for a specific date range.

### Office 365 Launch

Instead of desktop versions of Office, Microsoft is encouraging people to subscribe to an online version. If you'd like to see a preview of Office 365, you can attend Microsoft's online launch of the Office 365 product tomorrow, Wed. Feb. 27th. There are two sessions:

• 8 AM Pacific Time
• 5 PM Pacific Time

To register, click here, and fill in the registration form: Office 365 Launch Registration

There are lots of required fields in that registration form, but you can use the check boxes at the bottom, to control what type of email you get from Microsoft, after signing up.

### Sum Amounts in a Date Range

While you’re waiting for the Office 365 Launch to start, you could calculate how many units of your top product have been sold.

If you’re using Excel 2007 and later, use the SUMIFS function, and for earlier versions, use the SUMIF function.

In this example, a Start date and an End date are entered on the worksheet. Dates are in column A, and units sold are in column B.

### Use SUMIFS to Calculate Total for a Date Range

The SUMIFS function to calculate a total based on multiple criteria. We'll use a SUMIFS formula to total all the units where the sales date is:

• on or after the Start date
• AND
• on or before the End date.

Here is the formula that is entered in cell D5:

=SUMIFS(\$B\$2:\$B\$9,\$A\$2:\$A\$9,">=" & \$D\$2, \$A\$2:\$A\$9,"<=" & \$E\$2)

• Range \$B\$2:\$B\$9 has the numbers that we want to sum.
• Range \$A\$2:\$A\$9 contains the sales dates.
• The first criterion, ">=" & \$D\$2, is the range with the value for criteria 1 (the Start date), and the operator to use with that value (greater than or equal to)
• The second criterion, "<=" & \$E\$2, is the range with the value for criteria 2 (the End date), and the operator to use with that value (less than or equal to)

### Verify the Total

In this example, the result for the selected date range is a total of 494 units sold. To verify, you can select cells B3:B6, and look at the total shown in Excel's Status Bar.

To get the total units for a different date range, change the Start date in cell D2, and/or the End date in cell E2.

### Calculate Total for a Date Range with SUMIF

If you’re using Excel 2003, the SUMIFS function isn’t available, but you can calculate the total for a date range with the SUMIF function.

To see the details, and to download the sample file, visit my Contextures website: Sum Amounts in a Date Range with SUMIF

### Watch the Video

To see the steps for creating a SUMIFS formula, and verifying the total, please watch this short video tutorial.

__

Or watch on YouTube: How to Sum for a Specific Date Range in Excel

____________________

### 32 Responses

1. Piliphus says:

Thank you Debra Dalgleish, this formula is very useful for me.

2. Melissa Garza says:

How can this be done in Microsoft 365 Excel 2013, you mention there is a faster way to do it perhaps?

3. Martin says:

Hello Brenda,

I implemented your formula in excel 2010 on a check register for which I wanted the total monthly expenses display in a cell. However, the formula displays a wrong amount. If necessary, I can upload the excel file for your verification. I understand that I maybe doing something wrong too.

Thank You

4. Michael says:

Spent hours trying to find the best way to do this. This post was able to show me the EASIEST way to do it. Thank you very much! Big help!

This is indeed good a good stuff.But how would the formula would be if one column is added with different kinds of products name such as A,B,C,D & so on.Here now one more criteria for product is added i.e to say-2 date criteria and 1 product criteria with total 3 criteria.
I would be eargerly waiting for the reply.

6. Dianne says:

Is there a way to look up a specific date within a a range of dates to get find the amount in another column? For example I have monthly dates in column F (8=38) and date ranges starting in A2 and ending in B10. There are monthly billing amounts in C2 through C10. I want to look up the monthly billing amount for each month shown in in column F:

Column A Column B Column C (prem Amt)
Row 2 1/1/2012 12/31/2012 50.00
Row 3 1/1/2013 6/15/2013 100.00
Row 4 6/16/2013 10/1/2015 65.00
etc

Column F Column G (Prem Amt)
Row 8 01/01/2012
Row 9 08/01/2012
Row 10 04/01/2013

7. Johny says:

I try multiple ways to do a sum of amounts based on month dates from an nightmare workbook and this example just made my day.

Thank you soooooo much.

8. Eshwar says:

Thank You, Really it is helped me

9. Taylor says:

can this be used on horizontal data. I tried it and it is not working.

10. Iain Stevenson says:

Thanks this got me over a tricky problem.

11. voltik says:

Absolutely amazing way post!! Saved alot of time with this!

12. Classic Knight says:

Thank you. This was extremely useful; and not available anywhere else I searched.
Debra Rules!

13. Mel S says:

Excellent solution. Many thanks

14. Claire says:

Thanks so much, this explanation was simple and exactly what I needed!

15. charles says:

thanks! much appreciated!

16. Erika Schomberg says:

=COUNTIFS(Reported_Date,”>=1/1/2015″,Reported_Date,”<=1/31/2015",STATUS,"A")

17. Natalie says:

Hello! Thanks for this great post. I want to keep an excel sheet to track daily carbs, calories, fat and protein. I want extra fields beside either the first or last entry for each day that will automatically calculate daily totals based on the date, but not have to enter a start and end date. It should automatically calculate these for each entry under that date. I hope that makes sense. Is there such a function that will do this in excel so I don’t have to do each start and end date? Many thanks!

18. Marcos says:

Happen to have exaclty the same problem as (found this on youtube):

this is the first logical tutorial I’ve seen for this. Unfortunately its still not working for me. Its coming up with the #value! which i always interpreted as there are no values to add. However even when i put in values that should work, nothing comes up
I named the ranges so that I could use it as numbers get added (as a forever growing list) could this be part of the problem? does every cell need to have a value in order for the formula to work?
Thanks for any ideas!

Any ideas??? Cheers!

• @Marcos, select a few of those numbers, then look in the Status bar, at the bottom right of the Excel window.
Does it show a Count, and a Numerical Count?
If those totals are different, Excel isn’t seeing them as real numbers. There are suggestions on my website, for how to fix that: http://www.contextures.com/xlDataEntry03.html

19. Matt says:

Thanks a lot Debra, can you also help with the formula for choosing the same with different items. in the same example i have added 2 items, now i want the sum of “CAR”

Date UnitsSoldItem Start End
01-Jan 61 Car 06-Jan 04-Feb
01-Jan 60 Bike
09-Jan 122 Car
09-Jan 100 Bike
17-Jan 59 Car
25-Jan 194 Car
04-Feb 119 Car
04-Feb 120 Bike
10-Feb 69 Car
19-Feb 193 Car
25-Feb 188 Car

20. Matt says:

Thanks a lot Debra Dalgleish, this formula is very useful.

21. Mark says:

Debra, I am having trouble with this and I am at loss as to why. I have a file and I use this formula in other tabs and it works fine but for some reason it is not working in another tab.

=SUMIFS(Data!\$G:\$G,Data!\$A:\$A,\$D\$6,Data!\$B:\$B,\$E\$1,Data!\$C:\$C,”MMG”,Data!\$F:\$F,”<="&\$D\$7) If I take out the "<="& I get the information for a single month, but when I put it into the equation it give me a zero.

I am assuming it is something simple but I am not able to figure it out.

• @Mark, if you cut that formula, and paste it somewhere on the Data sheet, does it work correctly?
There were problems with SUMIFS in Excel 2010 when referencing a different sheet.

22. steve says:

Mnay thanks for for this is was ectramely helpfull in what i was doing

23. Kristiania Dudek says:

To add to the wonderful post…If you don’t want the Beginning and Ending Date to be on your spread sheet, replace the following:

D2 with Date(2016,01,06)
and
E2 with Date(2016,02,04)

Thank you so much for this post…I completely forgot the & with the greater than or less then. Life saver!

24. ARK says:

YOUR ARE A GENIUS!!! Thank you so much for putting this together!!!!

25. Casey5 says:

Hi Debra,
I like your formula very much. My date range in column A are all months only and the months are text not dates 9i.e January, February, etc.). How can I modify the formula to use text months in column A?

26. Elle says:

This formula is most understandable and effective! Thank you!

27. Shuman Maj says:

Trying to add up all expenses (col D), based on date (col A.) and Category (col E.) ….here is the formula I have been using. =SUMIFS(D:D,A:A,”>=”&DATE(2017,1,1),date,”<="&DATE(2017,12,31),E:E,"Payment”)