Sum if between two dates in Excel: SUMIFS with date range as criteria

Working on a report, investment plan or any other dataset with dates, you may often need to sum numbers within a specific period. This tutorial will teach you a quick and easy solution - SUMIFS formula with date range as criteria.

On our blog and other Excel forums, people often ask how to use SUMIF for date range. The point is that to sum between two dates, you need to define both dates while the Excel SUMIF function only allows one condition. Luckily, we also have the SUMIFS function that supports multiple criteria.

How to sum if between two dates in Excel

To sum values within a certain date range, use a SUMIFS formula with start and end dates as criteria. The syntax of the SUMIFS function requires that you first specify the values to add up (sum_range), and then provide range/criteria pairs. In our case, the range (a list of dates) will be the same for both criteria.

Considering the above, the generic formulas to sum values between two dates take this form:

Including bound dates:

SUMIFS(sum_range, dates,">="&start_date, dates, "<="&end_date)

Excluding bound dates:

SUMIFS(sum_range, dates,">"&start_date, dates, "<"&end_date)

As you can see, the difference is only in the logical operators. In the first formula, we use greater than or equal to (>=) and less than or equal to (<=) to include the lower and upper bound dates in the result. The second formula checks if a date is greater than (>) or less than (<), leaving out the start and end dates.

In the table below, suppose you want to sum projects that are due in a particular date range. To have it done, we enter the start date in F1, the end date in G1, and use the following formula to add up the budgets in B2:B10 between these two dates, inclusive:

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)

Formula to sum data between 2 dates

If you prefer hardcoding a date range in the formula, then type a date right after the logical operator and enclose the whole criteria in quotation marks like this:

=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")

To avoid possible mistakes, you can supply dates with the help of the DATE function:

=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&DATE(2020,9,20))

Sum within a dynamic range based on today's date

In situation when you need to sum data within a dynamic date range (X days back from today or Y days forward), construct the criteria by using the TODAY function, which will get the current date and update it automatically.

For example, to sum budgets that are due in the last 7 days including todays' date, the formula is:

=SUMIFS(B2:B10, C2:C10, "<="&TODAY(), C2:C10, ">"&TODAY()-7)

If you'd rather not include the current date in the final result, use the less than operator (<) for the first criteria to exclude today's date and greater than or equal to (>=) for the second criteria to include the date which is 7 days before today:

=SUMIFS(B2:B10, C2:C10, "<"&TODAY(), C2:C10, ">="&TODAY()-7)

Sum values within a range based on today's date

In a similar manner, you can sum values if a date is a given number of days forward.

For example, to get a total of budgets that are due in the next 3 days, use one of the following formulas:

Today's date is included in the result:

=SUMIFS(B2:B10, C2:C10, ">="&TODAY(), C2:C10, "<"&TODAY()+3)

Today's date is not included in the result:

=SUMIFS(B2:B10, C2:C10, ">"&TODAY(), C2:C10, "<="&TODAY()+3)

Sum values for a given number of days forward from today

Sum if between two dates and another criteria

To sum values within a date range that meet some other condition in a different column, simply add one more range/criteria pair to your SUMIFS formula.

For example, to sum budgets within a certain date range for all the projects that contain "tip" in their names, extend the formula with a wildcard criteria:

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1, A2:A10, "tip*")

Where A2:A10 are the project names, B2:B10 are the numbers to sum, C2:C10 are the dates to check, F1 is the start date and G1 is the end date.

Of course, nothing prevents you from entering the third criteria in a separate cell too, and referencing that cell like shown in the screenshot:
Formula to sum if between two dates and another criteria

SUMIFS date criteria syntax

When it comes to using dates as criteria for Excel SUMIF and SUMIFS functions, you wouldn't be the first person to get confused :)

Upon a closer look, however, all the variety of use cases boils down to a few simple rules:

If you put dates directly in the criteria arguments, then type a logical operator (>, <, =, <>) right before the date and enclose the whole criteria in quotes. For example:

=SUMIFS(B2:B10, C2:C10, ">=9/10/2020", C2:C10, "<=9/20/2020")

When a date is input in a predefined cell, provide criteria in the form of a text string: enclose a logical operator in quotation marks to start a string and use an ampersand (&) to concatenate and finish the string off. For instance:

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)

When a date is driven by another function such as DATE or TODAY(), concatenate a comparison operator and a function. For example:

=SUMIFS(B2:B10, C2:C10, ">="&DATE(2020,9,10), C2:C10, "<="&TODAY())

Excel SUMIFS between dates not working

In case your formula is not working or producing wrong results, the following troubleshooting tips may shed light on why it fails and help you fix the issue.

Check the format of dates and numbers

If a seemingly correct SUMIFS formula returns nothing but zero, the first thing to check is that your dates are really dates, and not text strings that only look like dates. Next, make certain you are summing numbers, and not numbers stored as text. The following tutorials will help you spot and fix these issues.

Use the correct syntax for criteria

When checking dates using SUMIFS, a date should be put inside the quotation marks like ">=9/10/2020"; cell references and functions should be placed outside the quotes like "<="&G1 or "<="&TODAY(). For full details, please see date criteria syntax.

Verify the formula's logic

A small typo in a budget could cost millions. A little mistake in a formula might cost hours of debugging time. So, when summing between 2 dates, check if the start date is preceded by the greater than (>) or greater than or equal to (>=) operator and the end date is prefixed by less than (<) or less than or equal to (<=).

Make sure all ranges are the same size

For the SUMIFS function to work correctly, the sum range and criteria ranges should be equally sized, otherwise a #VALUE! error occurs. To fix it, ensure that all criteria_range arguments have the same number of rows and columns as sum_range.

That's how to use the Excel SUMIFS function to sum data in a date range. If you have some other interesting solutions in mind, I'll be really grateful if you share in comments. Thank you for reading and hope to see you on our blog next week!

Practice workbook for download

SUMIFS date range examples (.xlsx file)

You may also be interested in

22 responses to "Sum if between two dates in Excel: SUMIFS with date range as criteria"

  1. Kirk says:

    I am trying to develop an Excel formula to obtain a count.

    If an ID number appears multiple times in one column and the result “received” appears one or more times in a different column, what is the formula to count only 1 instance of the occurrence of the ID and “received”?
    For example:

    Column Column
    F J
    5596326 attempted
    5596326 delivered
    5596326 received
    5596326 received
    5596326 sent
    5596326 Wrong Number

    The ID number appears 6 times in column F, and “received” appears twicein column J. With a correct formula, the result should be 1.

    What is the formula?

    Thank you for any assistance.

    • Sachin Oak says:


      Not sure if I understood your ask here, but try below formula in H1.


      5596326 attempted 1
      5596326 delivered 1
      5596326 received 2
      5596326 received 1
      5596326 sent 1
      5596326 Wrong Number 1

  2. SUNIL says:

    Very helpful. Thanks a lot.

  3. Anthony Harrison says:

    I use the following file as the source of my SUM(IF(COUNTIFS formula, a revision of one used in your blog for "Sum if between two dates and another criteria":

    Date Injury Location Gender Age Group Incident Type Plant Report Type Shift Department Incident Cost Days Lost

    1-Jan-20 Multiple Male 25-34 Burn Iowa Near Miss Afternoon Painting $- 0.00
    3-Jan-20 N/A Male 35-49 Vehicle Alabama Lost Time Day Fabrication $3,367 0.50
    3-Jan-20 Eye Male 18-24 Cut Georgia Near Miss Day Administration $- 0.00

    My formula [=SUM(IF(Data!$A$2:$A$515=DATE(2020,1,1)),1/COUNTIFS(Data!$F$2:$F$515,$A3,Data!$J$2:$J$515,">=100",Data!$A$2:$A$515,"="&DATE(2020,1,1))),0)]

    It returns an unexpected value of 18 to display the number of incident each month at each plant which reported plant injury costs which exceed $100. For Iowa, the expected return value is 9 for the month of January.

  4. Mairaj says:

    i have a question for formula...
    I have to made a report that contains different items. the report is based on another sheet which contains different items that had issued received , put on production on many dates. I have to made formula that allow range of dates of one particular items to be appear says in production line.

    Please help me in this regards.

  5. Kevin says:

    I have what I'm assuming is a straightforward formula challenge that I can't seem to figure out.

    I have a table of data with the headers "Date,START TIME, END TIME, USAGE, UNITS, COST". The START TIME and END TIME is an hour block of time (e.g., 0:00 & 0:59, up to 23:00 & 23:59).

    In adjacent cells I have, say J17 & J18 with a pull down list of dates, that I want to use in a "greater than or equal to 02/01/2021 and less than or equal to 02/28/2021, in a referencing sub table if you will immediately adjacent to these date parameters, listed as "START TIME", "COST", and "USAGE".

    In the cells under this sub table, I want to place formulas that do this:
    * Given the date interval cells, search the main table's START TIME (categories = hour blocks) and sum the values for COST and USAGE.

  6. Andy says:

    I would like to do a similar thing but kind of opposite and was wondering if you can help.
    We have a spreadsheet with events (multiple days) and numbers of staff e.g.

    Start date End date Number of staff
    1 Jan 21 3 Jan 21 5
    2 Jan 21 2 Jan 21 3
    5 Jan 21 10 Jan 21 10

    What I would ideally like is to then have a second sheet with all the dates of the year in column A and then how many staff are working on that date so with the above it would look like
    1 jan 21 5
    2 jan 21 8
    3 jan 21 5
    4 jan 21
    5 jan 21 10
    6 Jan 21 10


    Is that possible?



  7. Jonah S says:

    Thank you very much sir!

  8. Gans says:

    I have question for you as next;
    exam: Start dates dest date Year month day
    1983-02-07 1989-03-19 6 1 12
    1989-04-03 1994-10-08 5 6 5
    1994-10-08 2000-12-31 6 2 23
    sum() ? ? ?
    I want to find and to know it 8 sum of year , months, days by use excel function

  9. Steven says:

    My Start date is in one collume and my end date is in another - i cant seem to get this to work?

    Example 1: =SUMIFS(Sheet2!$Q:$Q,Sheet2!$I:$I, ">="&Sheet1!B4, Sheet2!$J:$J, "="&1/4/2021,Sheet2!J:J,"<="&1/4/"2021")

  10. Steven says:

    Sorry about that! I did reread it again and I have attempted a few different things.
    let me try by using a simple version of what I would like to accomplish: =SUMIFS(J:J, H:H, ">="&B6, I:I, "<="&B6) H = is my Start Date, I = my End Date, J is the values i would like to sum if they are inside the date listed in B6

  11. Mike says:

    I am tracking productivity for a department and want to provide weekly updates to our team in the form of previous week averages. I'm trying to create a quick calculator where I can simply change the Start/End Dates and it recalculates the averages automatically.

    The formula I have below keeps returning @DIV/O! errors. C2:KW2 is the portion of the row where daily productivity will be input. C1:KW1 is the row with each days date in it. A36 and A39 are the cells that are dynamic and where I would input the date range I am trying to calculate.

    =AVERAGEIFS(C2:KW2, C1:KW1, ">="&A36, C1:KW1, "<="&A39)

  12. Octavia says:

    From an invoicing perspective, each day there is a charge for service (prorated amount) based on a monthly amount.

    I would like to calculate the dollar amount for a service for 'Date A' through 'Date B' (both Date A & B are billable days).

    The spreadsheet to include a user defined 'Date A' (ie: 01/15/21), 'Date B' (ie: 04/16/21) and 'Monthly $' (ie: $10.00)

    January there are 17 billable days (01/15/21 thru 01/31/21) * 0.0323 (daily prorate) = $5.48 (prorated amount of the 'Monthly $')
    February there are 28 billable days = $10.00 ('Monthly $')
    March there are 31 billable days = $10.00 ('Monthly $')
    April there are 15 billable days (04/15/21 thru 04/30/21) * 0.5000 (daily prorate) = $5.00 (prorated amount of the 'Monthly $')

    A1 = 'Data A'
    A2 = 'Date B'
    A3 = 'Monthly $'
    A4 = ? - hoping for a formula here

    • Octavia says:


      April there are 15 billable days (04/01/21 thru 04/15/21) * 0.0333 (daily prorate) = $5.00 (prorated amount of the 'Monthly $')

    • Hello!
      If I got you right, the formula below will help you with your task:

      =DATEDIF(A1,A2,"m")*A3 + (EOMONTH(A1,0)-A1+1)*A3/(EOMONTH(A1,0)-EOMONTH(A1,-1)) + (A2-EOMONTH(A2,-1))*A3/(EOMONTH(A2,0)-EOMONTH(A2,-1))

      You can learn more about DATEDIF function in Excel in this article on our blog.

      Hope this is what you need.

  13. Octavia says:


    The spreadsheet to include a user defined 'Date A' (ie: 01/15/21), 'Date B' (ie: 04/15/21) and 'Monthly $' (ie: $10.00)

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)