How to Use SUMIFS to SUM Values in Date Range in Excel

To calculate sum values with conditions in a date range is a very common and tricky task in Excel. But Excel has the SUMIFS function to do this job easily and accurately. In this article, we will show you how to use the SUMIFS function to SUM values in the date range in Excel.


Download Practice Template

You can download the free practice Excel template from here.


What is SUMIFS Function?

The SUMIFS function is used to find the sum of a range of values given certain conditions.

Generic Syntax:

=SUMIFS(range1, range2, “>”&condition1, range2, “<”&condition2)

Here,

  • range1 = the range to sum values
  • range2 = the range to match the given conditions
  • condition1 = minimum range
  • condition2 = maximum range

If you want to calculate the SUM including the given conditions, then the generic syntax will become,

=SUMIFS(range1, range2, “>=”&condition1, range2, “<=”&condition2)

Notice the difference, when you want to measure the values without the conditions, then you have to calculate with the less than (<) or greater than (>) operators. But when you want to calculate along with the values of the conditions, then those operators become less than or equal to (=<) and greater than or equal to (=>).


11 Criteria to Implement SUMIFS Function to SUM Values in Date Range in Excel

In this section, we will show you quite a number of criteria in the implementation of the SUMIFS function in the date range to SUM values in Excel.

1. Using SUMIFS Formula to SUM Values between Two Dates in Excel

As this is the very first criteria that you will learn, so we are starting from the very basic use of SUMIFS to SUM values between two dates.

Consider the following example, we want to retrieve the total Amount of Product manufactured from the date 5/5/2019 to 5/10/2019. So, we will see how to calculate the Total Amount of the Product from the given date range by using SUMIFS in Excel.

Steps to get the SUM values between two dates using SUMIFS are given below.

Steps:

  • First, store the given start date and end date in the worksheet. In our case, we stored the Start Date in Cell H4 and End Date in Cell H5.
  • Next, in the result cell (in our case, it was the cell beside Total Amount, Cell H6), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<="&H5)

sumifs date range between two dates

In the result cell (Cell H6), you will get the SUM values (1602) of the amount between two given dates.

Formula Explanation:

It will be easier to understand how the formula works if we compare this formula with the generic formula of SUMIFS.

The generic formula of SUMIFS is,

=SUMIFS(range1, range2, “>=”&condition1, range2, “<=”&condition2)

Where,

  • range1 = the range to sum values
  • range2 = the range to match the given conditions
  • condition1 = minimum range
  • condition2 = maximum range

And the formula of this criteria is,

=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<="&H5)

So, what’s happening here is,

  • D5:D15 = the column range to sum values of the Amount
  • E5:E15 = the column range to match with the conditions
  • H4 = Start Date, condition1
  • H5 = End Date, condition2

Read more: How to Use SUMIFS with Date Range and Multiple Criteria


2. Implementing SUMIFS to SUM Values Based on a Condition in a Date Range in Excel

What if you want to calculate the SUM value of a given date range but with a certain condition? SUMIFS can let you do that easily.

Look at the following example, where we want to calculate the total manufactured Amount of only the Product “Jersey” from the date 5/4/2019 to 5/10/2019. So, we will see how to calculate the Total Amount of only “Jersey” from the given date range by using SUMIFS in Excel.

Steps to get the SUM values between two dates along with one condition using SUMIFS are given below.

Steps:

  • First, store the given start date and end date in the worksheet. In our case, we stored the Start Date in Cell H4 and End Date in Cell H5. Also, store the condition in another cell (e.g. “Jersey” in Cell H6).
  • Next, in the result cell (in our case, it was the cell beside Total Amount, Cell H7), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<="&H5, C5:C15, H6)

sumifs date range with condition

In the result cell (Cell H7), you will get the SUM values (579) of the amount of a certain condition/product (“Jersey”) between a date range.

Formula Explanation:

The generic formula to calculate SUMIFS in date range with a condition is,

=SUMIFS(range1, range2, “>=”&date1, range2, “=<”&date2, condition)

And the formula of this criteria is,

=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<="&H5, C5:C15, H6)

Which means,

  • D5:D15 = the column range to sum values of the Amount
  • E5:E15 = the column range to match with the conditions
  • H4 = Start Date, condition1
  • H5 = End Date, condition2
  • H6 = the product (“Jersey”)

So what happened inside the formula is pretty much the same as the formula of the previous section. Here we just added the cell reference number (H6) of the cell that contains the condition/product (“Jersey”) at the end of the formula to calculate only the amount of this specific product.


3. Implementing SUMIFS to SUM Values Except for a Specific Condition in a Date Range in Excel

You have just learnt how to calculate the SUM value between date range with a certain product. But what if you want to get the total between two dates but this time, it will be except for a specific product?

Well, the procedure is almost the same as the previous section when you calculated the Total Amount for a certain Product, but with a simple catch. Let’s find out what the catch is with the help of the example below.

Steps to get the SUM values between two dates except for one condition using SUMIFS are given below.

Steps:

  • First, store the given start date and end date in the worksheet. In our case, we stored the Start Date in Cell H4 and End Date in Cell H5. Also, store the condition that you want to exclude in another cell (e.g. “Jersey” in Cell H6).
  • Next, in the result cell (in our case, it was the cell beside Total Amount, Cell H7), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<="&H5, C5:C15, "<>"&H6)

sumifs date range except condition

In the result cell (Cell H7), you will get the SUM values (1035) of the amount between a given date range except for a certain condition/product (“Jersey”).

Formula Explanation:

If we compare this formula with the generic formula of SUMIFS with a condition, then it will be easier to understand how the formula worked.

As we have already known from the previous section of the generic formula to calculate SUMIFS in date range with a condition is,

=SUMIFS(range1, range2, “>=”&date1, range2, “=<”&date2, condition)

And the formula for this criteria is,

=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<="&H5, C5:C15, "<>"&H6)

which follows the syntax of,

=SUMIFS(range1, range2, “>”&date1, “<”&date2, “<>”condition)

As you can see, the difference is only with the prefix symbol of the condition. When we want to include a condition to calculate the total with a certain date range, then we simply write the cell reference number.

But when we need to calculate the total in a given date range by excluding a condition, then we need to put the “<>” symbol before the cell reference number to calculate values without a specific condition.


4. Applying SUMIFS to Calculate a Dynamic Date Range Based on Current Date in Excel

Sometimes you may want to know the SUM value of certain days before or after your current date. Look at the following example where we want to get the total amount of 10 days before Today. Let’s find out how to get that using SUMIFS.

Steps to get the SUM values of a date range based on the current date using SUMIFS are given below.

Steps:

  • First, in a cell, store the number of days before or after the current date that you want to check. In our case, we stored 10 days before Today in Cell H6 (you can store any amount of days that you require).
  • Next, in the result cell (in our case, it was the cell beside Total Amount, Cell H7), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, "<"&TODAY(), E5:E15, ">="&TODAY()-H6)

sumifs date range based on current date

In the result cell (Cell H7), you will get the SUM values (1308) of the amount between the current date and the given amount of days before.

Formula Explanation:

Again, let’s compare this formula with the generic formula of the SUMIFS to understand better.

The generic formula of SUMIFS is,

=SUMIFS(range1, range2, “>”&condition1, range2, “<”&condition2)

And the formula for this criteria is,

=SUMIFS(D5:D15, E5:E15, "<"&TODAY(), E5:E15, ">="&TODAY()-H6)

In Excel, the TODAY() function returns the current date.

  • So here, we are passing TODAY() as the argument, so that it calculates the SUM value according to the current date.
  • And in the second argument, we are subtracting (-) the number of days (10 days stored in Cell H6) that we want our current date to scan through to extract the sum values.
    • If you want to calculate the values between the current date and the days ahead of it, then simply add (+) the number of days with TODAY() instead of subtracting it.

5. Applying SUMIFS to Calculate SUM Values between a Given Date and Current Date in Excel

Using Excel’s SUMIFS function, you can also calculate the SUM values of products based on a user-given date and the current date.

Consider the following dataset, where we will be applying the SUMIFS to extract the total amount of the manufactured products between the current date and the given date 10/17/2021.

Steps to get the SUM values between a given date and the current date using SUMIFS are given below.

Steps:

  • First, in a cell, store the given date (e.g. 10/17/2021 in Cell H6).
  • Next, in the result cell (in our case, it was the cell beside Total Amount, Cell H7), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, ">"&H6, E5:E15, "<"&TODAY())

sumifs date range between given and current date

In the result cell (Cell H7), you will get the SUM values (2887) of the amount between the current date and a user-given date.

Formula Explanation:

If you check the similarities between the formula of this criteria and the generic formula of the SUMIFS then you will find the explanation a lot easier.

The generic formula of SUMIFS is,

=SUMIFS(range1, range2, “>”&condition1, range2, “<”&condition2)

And the formula of this criteria is,

=SUMIFS(D5:D15, E5:E15, ">"&H6, E5:E15, "<"&TODAY())

As you can clearly see,

  • Here we are passing the user-given date (H6) as our first condition, condition1, and
  • TODAY() as our second condition, condition2, to calculate the values between these dates.

6. Utilizing SUMIFS Formula to SUM Values in a Specific Month in Excel

Until now we have been working with days, but now we will discuss how to utilize SUMIFS in calculating values in a specific month.

Look at the following dataset which we will consider as our example to extract the SUM values of the Amount of Product in the month of November.

Steps to get the SUM values of a specific month using SUMIFS are given below.

Steps:

  • First, in a cell, store the month (e.g. November in Cell H4).
  • Next, in the result cell (in our case, it was the cell beside Total Amount, Cell H5), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<="&EOMONTH(H4,0))

sumifs date range in specific month

In the result cell (Cell H5), you will get the SUM values (2911) of the amount of the manufactured products of November month.

Formula Explanation:

Excel’s EOMONTH function is used to find the last day of the month.

Syntax:

=EOMONTH(start_date, month)

Here,

  • start_date = represents the starting date.
  • month = number of months before or after the start_date.

The generic formula of SUMIFS is,

=SUMIFS(range1, range2, “>=”&condition1, range2, “<=”&condition2)

And the formula of this criteria is,

=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<="&EOMONTH(H4,0))

Here,

  • we are passing the given month (November in Cell H6) as our first condition, condition1, and
  • EOMONTH() as our second condition, condition2, to store the last day of the month to calculate SUM values of the month November.

Similar Readings:


7. Utilizing SUMIFS Formula to SUM Values for the Current Month in Excel

Before, you learned how to get the SUM values for any given month. But in this section, you will learn how to get the SUM values of the current month. From the example below, we will be extracting the SUM values of the current month (December, when writing this article).

Steps to get the SUM values of the current month using SUMIFS are given below.

Steps:

  • In the result cell (in our case, it was the cell beside Total Amount, Cell H6), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, ">="& EOMONTH(TODAY(),-1)+1, E5:E15, "<="& EOMONTH(TODAY(),0))

sumifs date range current month

In the result cell (Cell H6), you will get the SUM values (1197) of the amount of the manufactured products of the current month.

Formula Explanation:

The generic formula of SUMIFS is,

=SUMIFS(range1, range2, “>=”&condition1, range2, “<=”&condition2)

And the formula of this criteria is,

=SUMIFS(D5:D15, E5:E15, ">="& EOMONTH(TODAY(),-1)+1, E5:E15, "<="& EOMONTH(TODAY(),0))

As this formula is a bit tricky so we will break this down into smaller pieces to understand clearly.

Breakdown of the first condition,

  • EOMONTH(TODAY(),-1)+1
    • TODAY() -> returns the current date.
    • EOMONTH(TODAY(),-1) -> gives us the last day of the month that falls exactly one month before today. Meaning, the last day of the last month.
    • EOMONTH(TODAY(),-1)+1 -> the first day of this month obviously falls one day after that, so we simply add a 1 to the date returned by this function.
  • The second condition, EOMONTH(TODAY(),0) -> to find the last day of the current month.

This is how we are getting the SUM values of the current month using SUMIFS in Excel.


8. Utilizing SUMIFS Formula to SUM Values from a Given Date to the End of the Month in Excel

This section with the help of the following example will let you know how to get the SUM value from any given date of a month to the end of that exact month.

Steps to get the SUM values from a given date to the end of the month using SUMIFS are given below.

Steps:

  • First, in a cell, store the date of the month (e.g. 5/6/2019 in Cell H4).
  • Next, in the result cell (in our case, it was the cell beside Total Amount, Cell H5), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<"&EOMONTH(H4,0))

sumifs date range between given and end of the month

In the result cell (Cell H5), you will get the SUM values (1368) of the amount of the manufactured products from the given date to the end of that month.

Formula Explanation:

The explanation of this criteria is pretty straightforward.

We already know the generic formula of SUMIFS, which is,

=SUMIFS(range1, range2, “>”&condition1, range2, “<”&condition2)

And the formula of this criteria is,

=SUMIFS(D5:D15, E5:E15, ">="&H4, E5:E15, "<"&EOMONTH(H4,0))

As we already know that EOMONTH() returns the last day of the month so,

  • we pass the given date (H4) as the first argument of the formula, and
  • the EOMONTH(H4, 0) function as our second argument in the formula

to calculate the SUM values between a given date and the end date of that exact month.


9. Using SUMIFS Formula to Calculate SUM Values in a Specific Year in Excel

You have learned how to calculate values between date ranges of days and months, now it’s time for you to learn about years too.

With the help of the following example, we will know how to get SUM values of the product’s amount in a specific year using SUMIFS.

Steps to get the SUM values in a specific year using SUMIFS are given below.

Steps:

  • First, in a cell, store the year (e.g. 2019 in Cell H4).
  • Next, in the result cell (in our case, it was the cell beside Total Amount, Cell H5), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, ">="&DATE(H4,1,1), E5:E15, "<="&DATE(H4,12,31))

sumifs date range in year

In the result cell (Cell H5), you will get the SUM values (1725) of the amount of the manufactured products in the year 2019.

Formula Explanation:

Excel’s DATE function is used to create a date with year, month and day.

Syntax:

=DATE(year, month, day)

Here,

  • year = number for the year.
  • month = number for month.
  • day = number for day

So let’s compare the generic SUMIFS formula with the formula of this criteria to understand how this formula works.

The generic formula of SUMIFS is,

=SUMIFS(range1, range2, “>=”&condition1, range2, “<=”&condition2)

And the formula of this criteria is,

=SUMIFS(D5:D15, E5:E15, ">="&DATE(H4,1,1), E5:E15, "<="&DATE(H4,12,31))

Here we are passing the DATE() function as our conditions,

  • DATE(H4,1,1)
    • DATE(2019,1,1)
      • the year 2019, month 1 (January), day 1 (first day of the year)
  • DATE(H4,12,31)
    • DATE(2019,12,31)
      • the year 2019, month 12 (December), day 31 (last day of the year)

to calculate the SUM values between a year.


10. Using SUMIFS to SUM Values of a Date Range from Multiple Columns in Excel

What if you have products that haven’t finished manufactured yet. So, there’s still empty cells in your worksheet where the End Date is supposed to be.

Look at the following example to understand what we have just said. Now, you will learn how to calculate the SUM value of the amount of the products that are already done manufactured and are stored in a scattered way in the worksheet.

Steps to get the SUM values between two dates from multiple columns using SUMIFS are given below.

Steps:

  • First, store the start date in a cell (e.g. 11/10/2021 in Cell I4).
  • Then, store the End Date as Not Empty in another cell (e.g. Not Empty in Cell I5).
    • What is actually happening here is, the products that haven’t been manufactured yet don’t have the end date, therefore we are storing the Not Empty condition as our second argument.
  • Next, in the result cell (in our case, it was the cell beside Total Amount, Cell H7), write the following formula and press Enter.
=SUMIFS(D5:D15, E5:E15, I4, F5:F15, "<>")

sumifs date range multiple columns

In the result cell (Cell H7), you will get the SUM values (1899) of only the amount of the products that have both start and end dates.


11. Using SUMIFS to SUM Values of a Date Range from Another Sheet in Excel

Until now, we have been working on the same worksheet to get the SUM value of a date range. But what if you have a dataset stored in one sheet and you want to get the SUM value in another sheet?

Look at the following picture where we have only the data in the worksheet named Data Sheet.

datasheet for sumifs date range

And in another sheet named Total Sheet, we want to get the result.

total sheet for sumifs date range

So let’s find out how to do that using the SUMIFS function in Excel.

Steps to get the SUM value of a date range from one sheet to another using SUMIFS are given below.

Steps:

  • In the result cell (in our case, it was the cell beside Total Amount, Cell C6 in the Total Sheet), write the formula according to the generic syntax of SUMIFS.

Formula like this,

=SUMIFS(D5:D15, E5:E15, ">="&C4, E5:E15, "<="&C5)

But as you don’t have any data in the running sheet (e.g. Total Sheet), so pressing Enter won’t give you any result.

  • To resolve the error, place the pointer of your mouse just before the value ranges (D5:D15, E5:E15) and click on the sheet that has the data (e.g. Data Sheet).
    This will auto-generate the name of the Data Sheet just before the value range, therefore all the data of that sheet will be a property to the Total Sheet (see the picture below to understand more).

sumifs date range from different sheet

  • Press Enter.

sumifs date range with multiple sheet

You will get the SUM values (1725) of the amount between a given date range in the result cell (Cell C6) in the result sheet (e.g. Total Sheet).


Conclusion

This article showed you how to use the SUMIFS function to SUM values in the date range in Excel in 11 different ways. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo