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.

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

__

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

____________________

13 comments to Sum For a Date Range in Excel

  • Piliphus

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

  • Melissa Garza

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

  • Martin

    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

  • Michael

    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!

  • paradise

    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.

  • Dianne

    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

  • Johny

    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.

  • Eshwar

    Thank You, Really it is helped me

  • Taylor

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

  • Iain Stevenson

    Thanks this got me over a tricky problem.

  • voltik

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

  • Classic Knight

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

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>