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

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