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 the threshold dates:

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

Excluding the threshold 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 threshold 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, inclusive. To have it done, use this formula:

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

If you'd rather not hardcode a date range in the formula, then you can type the start date in F1, the end date in G1, concatenate the logical operators and cell references and enclose the whole criteria in quotation marks like this:

=SUMIFS(B2:B10, C2:C10, ">="&F1, C2:C10, "<="&G1)
Formula to sum data between 2 dates

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)

Table of contents

210 comments

  1. Dear all please help how to sum between two dates

    Month Rate Per Day Start date End Date
    January-23 $225.81 15-01-23 07-02-23
    February-23 $250.00
    March-23 $225.81
    April-23 $233.33
    May-23 $225.81
    June-23 $233.33
    July-23 $225.81
    August-23 $225.81
    September-23 $233.33
    October-23 $225.81
    November-23 $233.33
    December-23 $225.81

  2. Sir,,

    Please help in suming up sales, within specific time in a day. Eg
    10 Jan 23
    Like
    10:00 AM 10 pieces
    10:50AM 50 pieces
    11: 45AM 20 pieces

    I need to count same way sum of sales between 10 AM to 11AM which should come 60 automatically

  3. Column A Buy Date | Column B Sell Date | Column C Profit
    31/3/2022 | 31/3/2022 | $100
    31/5/2022 | 31/5/2022 | $50
    31/3/2022 | 10/4/2022 | $20
    31/8/2022 | 31/8/2022 | $10
    11/4/2022 | 30/4/2022 | $100

    Any formula that can do a sum if of profit if Days held is
    0 ($160 in this case: row 1, 2, 5)
    0-7 days ($0 in this case)
    8-14 days ($20 in this case: row 3)

    Trying to do this without adding an extra column in that does Col B - Col A
    15-22 days ($100 in this case: row 5)

  4. duty shift& time name revenue
    Morning 08-02 A 200
    Morning 09-03 b
    Morning 10-04 A 152
    evening 08-02 b
    evening 09-03 A 100
    evening 10-04 b 3

    count formula to get no of staff worked day and night separately (blank cells not worked)

  5. Thanks! That was the fix!

  6. Hi, thanks in advance for any help, i'm struggling to find a solution but I'm sure it's simple!
    I'm trying to sum a range if another range contains the current year, year-1 and current year +1 i.e.

    Dec 2021 = £300
    Jan 2022 = £100
    Feb 2022 = £400
    Jan 2023 = £200

    Output should be 2021 = £300 2022 = £500 2023 = £200

    I've tried =SUMIFS(B15:M15,B14:M14,”>=”&DATE(C7,1,1),B14:M14,”<=”&DATE(C7,12,31))

    B15:M15 are costs per month. B14:M14 are dates pulled through from another sheet.
    C7 is current year ( calculated by the formula =YEAR(A1) where A1 is today's date)

    • Use the below:
      For Previous Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7-1,15:15,"="&"01/01/"&C7,15:15,"="&"01/01/"&C7+1,15:15,"<="&"12/31/"&C7+1)

      • Prev Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7-1,15:15,"<="&"12/31/"&C7-1)

        • Current Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7,15:15,"<="&"12/31/"&C7)

          • Next Year: SUMIFS(16:16,15:15,">="&"01/01/"&C7+1,15:15,"<="&"12/31/"&C7+1)

  7. hi i want unique id based sum "= 5 Year" with one month date range........can you please help me ?

    Nikshya ID Date = 5 Year
    20286998 04-10-2021 2 4
    20286998 04-10-2021 0 0
    20285948 04-10-2021 5 6
    20285948 04-10-2021 0 0
    20363127 04-10-2021 2 2
    20363127 04-10-2021 0 0
    20363127 04-10-2021 0 0
    20363127 04-10-2021 0 0
    20162933 05-10-2021 1 2
    20162933 05-10-2021 0 0
    20162933 05-10-2021 0 0
    20162933 05-10-2021 0 0
    20162933 05-10-2021 0 0
    20162933 05-10-2021 0 0
    20522006 05-10-2021 2 2

    • Hi!
      Unfortunately, I do not understand what you are trying to calculate. Describe your problem in more detail. Write an example of the expected result.

  8. I have 50 employees, I need to sum the total labels picked in a time frame of an hour:
    example EEID# 1111 between the hours of 10:00 to 11:00 he picked 8 labels

    EEID# Total Labels Time
    1111 - 3 - 10:01
    1111 - 5 - 10:20

    • Hi!
      Have you tried the ways described in this blog post? Use time instead of a date condition. For example

      =SUMIFS(B2:B10, C2:C10, ">=10:00", C2:C10, "<=10:59")

      Or use the TIME function to specify the time. ">"&TIME(10,0,0)

  9. Hi, can anyone support, please? I need to sumif quantity of units picked in the warehouse by specific user from time 13:00-14:00.
    For example, this person "2345" have picked some quantity of units, and I need to filter out how many units this person did per hrs. It's 8hrs working day, and he picked all day, and I need to split by individual hrs. I hope this make sense?Thank you

    • Hello!
      You can use the formulas recommended in the article above. Add the necessary conditions to them.
      For example,

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

      F1 and G1 - start and end time. H1 - person.

  10. I am trying to have my salary auto update on the last working day of every month any help would be appreciated

  11. I am trying to figure out a formula to count time in hours but the date and time are in the same category which makes it hard to
    do the calculation. I need this calculation to determine how long we have had trucks on our yard. Below is an example

    Arrived_in_Yard Hit_the_Dock
    9/5/2022 16:27 9/8/2022 8:50

    How could I calculate this time in hours?

  12. Hi, I have a list of production hours per day for Aug month. 2 columns - Dates & Prod hours. In a separate cell, I need a formula to give me the sum of hours only for Sundays worked. Can you help please? Below is the data set for example:
    Dates Prod Hours
    Mon 8/1/22 113.68
    Tue 8/2/22 104.42
    Wed 8/3/22 123.18
    Thu 8/4/22 93.24
    Fri 8/5/22 84.70
    Sat 8/6/22 77.82
    Sun 8/7/22 68.29
    Mon 8/8/22 116.15
    Tue 8/9/22 135.33
    Wed 8/10/22 131.12
    Thu 8/11/22 99.14
    Fri 8/12/22 99.45
    Sat 8/13/22 83.66
    Sun 8/14/22 75.60
    Mon 8/15/22 112.50
    Tue 8/16/22 124.28
    Wed 8/17/22 131.24
    Thu 8/18/22 92.03
    Fri 8/19/22 100.12
    Sat 8/20/22 75.53
    Sun 8/21/22 74.86
    Mon 8/22/22 200.38
    Tue 8/23/22 179.04
    Wed 8/24/22 162.54
    Thu 8/25/22 131.83
    Fri 8/26/22 130.99
    Sat 8/27/22 68.26
    Sun 8/28/22 79.09
    Mon 8/29/22 111.81
    Tue 8/30/22 122.09
    Wed 8/31/22 124.22

    Thank you

  13. I'd like to sum up the quantity of one item in a month that is produced in yesterday and today, and I have this table below. Is that possible to use SUMIFS function? Let says today is 08/04/2022 and I want to sum up quantity of item B in today and yesterday.
    Item 8/1/2022 8/2/2022 8/3/2022 8/4/2022 8/5/2022 8/6/2022 8/7/2022 8/8/2022
    A
    B 14000 14000 14560 14000
    C
    D
    E
    F 9000 12800

      • Thanks for your support. That helps. However we have another case, let says there are few items in the table are duplicated, e.g 2 item A, 4 item B, 5 item C, each of them in a row. So which function need to use to sum up quantity of those items which have been produced yesterday and today?

  14. Dates Sale
    15-Jan-13 9
    15-Jan-13 2
    6-Oct-14 7
    16-Nov-09 8
    18-Jun-13 8

    Output should be

    Month Sale
    Jan 11
    Feb 0
    Mar 0
    Apr 0
    May 0
    Jun 8
    Jul 0
    Aug 0
    Sep 0
    Oct 7
    Nov 8
    Dec 0

  15. Hi guys,

    I am trying to do a =SUMIFS with a DATE RANGE and a wild card (notsure if thats the correct term)

    This is the formula that I am working with but it is either going "error" or just "zero".

    The date format from the data sheet is "10/16/2021 15:56:34"

    =SUMIFS('TDJ Sales Data'!$G$3:$G,'TDJ Sales Data'!$F$3:$F,">="&C1,'TDJ Sales Data'!$F$3:$F,"<="&C2"),'TDJ Sales Data'!$A$3:$A,"Armstrong"")

    • Hi!
      I can't check the formula that contains unique references to your workbook worksheets.
      I didn't find a situation where A3:A works (in Excel functions).
      If you want to refer to a range starting from A3 to the max row you can use this formula

      OFFSET(A3, 0, 0, ROWS(A:A)-2)

      Read more here: OFFSET formula to define an Excel dynamic named range.

      For example,

      =SUMIFS(OFFSET(A3, 0, 0, ROWS(A:A)-2),OFFSET(A3, 0, 0, ROWS(A:A)-2)," > "&C1)

  16. I have a spreadsheet for payroll where I have people who worked multiple shifts and I would like a column with a total for both shifts if there are 2. This is my lay out:

    Employee name (which would have to match), Date worked(this will have to match as well), hours for that shift (this is what I want to combine if possible)

  17. Good afternoon,

    I have two columns. 1 column with the date (25-04-2022 to 30-06-2022) and 1 column with a quantity. I want to add up the quantity per date. For example, for the date 25-4-2022, there are 20 columns with a quantity. How do I get an overview of the total quantities per date? I want to do this because I want to use this data to make a graph of the number of sales per day.

    As an example:
    Date; Quantity
    25-04 -2022; 3
    25-04-2022; 4
    25-04-2022; 8
    26-04-2022; 3
    26-04-2022; 6
    26-04-2022; 5
    27-04-2022; 3
    27-04-2022; 8
    27-04-2022; 3
    etcc

    • Hi!
      Have you tried the ways described in this blog post?
      Try the following formula:

      =SUMIFS(B2:B10, A2:A10, "="&DATE(2022,4,25))

      Use the DATE function to set the date you want. Or use a cell reference with a date.

  18. Hello and thank you in advance!
    I am trying to simply to add recorded hours for the week from Sunday-Saturday. The data exists in two simple columns, date in one, hours in the other. But, the days worked vary from week to week (sometimes a few days, sometimes all 7, hence, there will be varying numbers of rows between Saturdays). I would like a formula that finds "Saturday" in column A, then sums the hours for that calendar week (ie, that Sat and the preceding six days). Here's a sample three weeks of that data:

    Monday, March 18, 2019 10.03
    Tuesday, March 19, 2019 9
    Thursday, March 21, 2019 17.38
    Friday, March 22, 2019 9.72
    Saturday, March 23, 2019 5.52
    Monday, March 25, 2019 10.47
    Friday, March 29, 2019 9.28
    Saturday, March 30, 2019 9.47
    Sunday, March 31, 2019 11.45
    Monday, April 1, 2019 11.37
    Tuesday, April 2, 2019. 11.03
    Saturday, April 6, 2019 5.95

    • The problem is compounded by the fact that not every Saturday is a workday, so in some weeks the last day worked in a calendar week might be a Thursday to Friday.

      • Hello!
        Use the WEEKNUM function to determine the number of a week that begins on Sunday. Write the formula in column C :

        =WEEKNUM(A1,1)

        Use the SUMIF function to calculate your weekly total. Enter the following formula in cell D1 and then copy it down along the column:

        =IF(WEEKDAY(A1,2)=6,SUMPRODUCT(--($C$1:$C$20=WEEKNUM(A1,1)),$B$1:$B$20),"")

        You can add subtotals for column C to your table.
        I hope this will help, otherwise don't hesitate to ask.

  19. Hi,

    I'm using this for a personal month budget and I noticed that when I put in a month's date range, and then enter the next month's entries, it adds it to the previous month.

    For instance, let's say I have $1000 of income from 6/1 to 6/30 but when I put in income for July, it adds it to June's total AND July. Is there a way to have it only calculate for the dates in the formula and in between? Here is what I'm using:

    =SUMIFS(Input!B:B,Input!A:A,">=6/1/2022",Input!B:B,"<=6/30/2022")

  20. If one Date falls between 1st Jan2015 to 31st Dec'15, then if should show as X
    1st Jan2016 to 31st Dec'16, then if should show as Y
    1st Jan2017 to 31st Dec'17, then if should show as Z

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 :)