Sum For a Date Range in Excel

Sum For a Date Range in Excel

First, some news about the upcoming Office 365 launch, and then a tip on how to sum for a date range in Excel.

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.

sumdaterange01

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)

sumdaterange02

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.

sumdaterange03

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, watch this short video.

____________________

32 thoughts on “Sum For a Date Range in Excel”

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

  2. 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.
    I would appreciate your response.
    Thank You

  3. 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!

  4. 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.

  5. 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

  6. 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.

  7. 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!

  8. Happen to have exaclty the same problem as (found this on youtube):
    Elisabeth Bradley 2 years ago
    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!

  9. 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

  10. 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.

  11. 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!

  12. 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?

  13. 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”)

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.