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.

**Introduction to Excel 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

**manufactured from the date**

*Product***. So, we will see how to calculate the**

*5/5/2019 to 5/10/2019***of the**

*Total Amount***from the given date range by using**

*Product***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
in*Start Date***Cell H4**andin*End Date***Cell H5**. - Next, in the result cell (in our case, it was the cell beside
), write the following formula and press*Total Amount*, Cell H6**Enter**.

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

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

**from the date**

*Product*“Jersey”**5/4/2019 to 5/10/2019**. So, we will see how to calculate the

**of only “**

*Total Amount***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
in*Start Date***Cell H4**andin*End Date***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
), write the following formula and press*Total Amount*, Cell H7**Enter**.

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

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

**, but with a simple catch. Let’s find out what the catch is with the help of the example below.**

*Product*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
in*Start Date***Cell H4**andin*End Date***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
), write the following formula and press*Total Amount*, Cell H7**Enter**.

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

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
**T**), write the following formula and press*otal Amount*, Cell H7**Enter**.

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

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.

- If you want to calculate the values between the current date and the days ahead of it, then simply

**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
), write the following formula and press*Total Amount*, Cell H7**Enter**.

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

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

**in the month of**

*Product***.**

*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.
in*November***Cell H4**). - Next, in the result cell (in our case, it was the cell beside
), write the following formula and press*Total Amount*, Cell H5**Enter**.

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

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 (
in*November***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
), write the following formula and press*Total Amount*, Cell H6**Enter**.

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

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.
in*5/6/2019***Cell H4**). - Next, in the result cell (in our case, it was the cell beside
), write the following formula and press*Total Amount*, Cell H5**Enter**.

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

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.
in*2019***Cell H4**). - Next, in the result cell (in our case, it was the cell beside
), write the following formula and press*Total Amount*, Cell H5**Enter**.

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

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.
in*11/10/2021***Cell I4**). - Then, store the
as*End Date***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.

- 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

- Next, in the result cell (in our case, it was the cell beside
), write the following formula and press*Total Amount*, Cell H7**Enter**.

`=SUMIFS(D5:D15, E5:E15, I4, F5:F15, "<>")`

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

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

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
in the*Total Amount*, Cell C6), write the formula according to the generic syntax of*Total Sheet***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 thejust before the value range, therefore all the data of that sheet will be a property to the*Data Sheet*(see the picture below to understand more).*Total Sheet*

- Press
**Enter**.

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.