**Introduction to Excel SUMIFS Function**

**The SUMIFS function** is used to find the sum of a range of values with given 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

To calculate the **SUM** including the given conditions, the generic syntax becomes,

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

To measure values without the conditions, use the **less than (<) **or **greater than (>)** operators. To calculate with the values of the conditions, use **less than or equal to (=<) **and **greater than or equal to (=>)**.

**Example 1 – Using the SUMIFS Formula to SUM Values between Two Dates in Excel**

To get the total ** Amount** of

**manufactured from**

*Product*

*5/5/2019**to*

*5/10/2019:***Steps:**

- Select the cells to store the start and end date. Here,
**H4**and**H5**. - In
, enter the following formula and press*Total Amount*, H6**Enter**.

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

The result is **1602.**

**Formula Breakdown**

The generic formula of **SUMIFS** is,

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

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

The formula used is:

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

**D5:D15**= the column range to sum thevalues*Amount***E5:E15**= the column range to match the conditions**H4**=*Start Date,*condition1**H5**=**End Date, condition2**

**Example 2 – Using the**** SUMIFS to SUM Values Based on a Condition in a Date Range in Excel**

Calculate the total manufactured ** Amount** of the

**only, from**

*Product*“Jersey”**5/4/2019**to

**5/10/2019**.

**Steps:**

- Select the cells to store the start and end date. Here,
**H4**and**H5**. - Store the condition in another cell: “
**Jersey”**in**H6**. - In
, enter the following formula and press*Total Amount*, H7**Enter**.

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

The result is **579**.

**Formula Breakdown**

The generic formula is,

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

The formula used is:

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

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

**Example 3 – ****Using the SUMIFS to SUM Values, Except for a Specific Condition in a Date Range in Excel**

To get the total between two dates, except for a specific product:

**Steps:**

- Select the cells to store the start and end date. Here,
**H4**and**H5**. - Store the condition to exclude in another cell: “
**Jersey”**in**H6**. - In
, enter the following formula and press*Total Amount*, H7**Enter**.

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

The result is **1035**

**Formula Breakdown**

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

The formula used is:

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

which follows the **syntax** of:

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

The only difference is the **prefix** symbol **“<>”** in the condition, used to exclude a condition.

**Example 4 – ****Applying The SUMIFS to Calculate a Dynamic Date Range Based on the Current Date in Excel**

To get the total amount of **10** days before **Today:**

**Steps:**

- Select a cell to store the number of days before or after the current date that you want to check. Here,
**10**in**H6**. - In
, enter the following formula and press*Total Amount*, H7**Enter**.

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

The result is **1308**.

**Formula Breakdown**

The generic formula of **SUMIFS** is,

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

The formula used is:

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

**The TODAY() function** returns the current date.

**TODAY()**is the argument: it calculates the**SUM**value according to the current date.- The second argument subtracts
**(-)**the number of days (**10**in**H6**)- To calculate the values between the current date and the days ahead of it,
**add (+)**the number of days with**TODAY()**.

- To calculate the values between the current date and the days ahead of it,

**Example 5 – ****Applying the SUMIFS Function to Calculate the SUM Values between a Given Date and the Current Date in Excel**

Consider the following dataset. Extract the total amount of the manufactured products between the current date and the given date ** 10/17/2021**.

**Steps:**

- Select a cell, to store the given date (
**10/17/2021**in**Cell H6**). - In
, enter the following formula and press*Total Amount*, H7**Enter**.

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

The result is **2887**.

**Formula Breakdown**

The generic formula of the **SUMIFS** is,

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

The formula used is:

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

- The user-given date (
**H6**) is the first condition,**condition1,**and**TODAY()**the second condition,**condition2,**to calculate the values between these dates.

**Example 6 – ****Utilizing the SUMIFS Formula to SUM Values in a Specific Month in Excel**

Extract the **SUM** values of the ** Amount** of

**in**

*Product***.**

*November***Steps:**

- Select a cell to store the month (
in*November***H4**). - In
, enter the following formula and press*Total Amount*, H5**Enter**.

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

The result is **2911**.

**Formula Breakdown**

**The EOMONTH function** is used to find the last day of the month.

**Syntax:**

`=EOMONTH(start_date, month)`

**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)`

The formula used is:

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

- The given month (
in*November***H6**) is the first condition,**condition1,**and**EOMONTH()**is the second condition,**condition2**, used to store the last day of the month and calculate the**SUM**values in.*November*

**Example 7 – ****Utilizing the SUMIFS Formula to SUM Values for the Current Month in Excel**

Extract the **SUM** values of the current month (**December**).

**Steps:**

- In
, enter the following formula and press*Total Amount*, H6**Enter**.

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

The result is **1197**.

**Formula Breakdown**

The generic formula of the **SUMIFS** is,

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

The formula used is:

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

In the first condition,

**EOMONTH(TODAY(),-1)+1****TODAY()**-> returns the current date.**EOMONTH(TODAY(),-1)**-> returns the last day of the month, one month before today.**EOMONTH(TODAY(),-1)+1**-> the first day of this month is one day after the previous date, so 1 is added to the date returned by the function.

- The second condition,
**EOMONTH(TODAY(),0)**-> finds the last day of the current month.

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

Get the **SUM** value from any given date of a month to the end of that month.

**Steps:**

- Select a cell to store the date of the month (
in*5/6/2019***H4**). - In
, enter the following formula and press*Total Amount*, H5**Enter**.

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

The result is **1368**.

**Formula Breakdown**

The generic formula of the **SUMIFS** is,

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

The formula used is:

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

The **EOMONTH()** returns the last day of the month so,

- the given date (
**H4**) is the first argument of the formula, and the**EOMONTH(H4, 0)**function is the second argument in the formula, used to calculate the**SUM**values between a given date and the end date of that month.

**Example 9 – ****Using the SUMIFS Formula to Calculate SUM Values in a Specific Year in Excel**

Get the **SUM** values of the product amount in a specific year.

**Steps:**

- Select a cell to store the year (
in*2019***H4**). - In
, enter the following formula and press*Total Amount*, H5**Enter**.

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

The result is **1725**.

**Formula Breakdown**

**The DATE function** is used to create a date with year, month and day.

**Syntax:**

`=DATE(year, month, day)`

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

The generic formula of the **SUMIFS** is,

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

The formula used is:

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

**DATE()** is the 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 in a year.

**Example 10 – ****Using the SUMIFS function to SUM Values of a Date Range from Multiple Columns in Excel**

Calculate the **SUM** value of the amount of the products that are already manufactured and are stored in different cells in the worksheet.

**Steps:**

- Select a cell to store the start date (
in*11/10/2021***I4**). - Store the
as*End Date***Not Empty**in another cell:**I5**.- The products that haven’t been manufactured don’t have an end date. The
**Not Empty**condition is the second argument.

- The products that haven’t been manufactured don’t have an end date. The

- In
, enter the following formula and press*Total Amount*, H7**Enter**.

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

The result is **1899**.

**Example 11 – ****Using the SUMIFS to SUM Values of a Date Range from Another Sheet in Excel**

The data below is in the ** Data Sheet **worksheet.

In another sheet: ** Total Sheet**, you want to get the result.

**Steps:**

- In the result cell (here,
**C6**in the), enter the formula.*Total Sheet*

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

As you don’t have any data in the running sheet (** Total Sheet**), pressing

**Enter won’t return a result**.

- Place the
**pointer of**the mouse before (**D5:D15**,**E5:E15**) and**click**the.*Data Sheet*

The name of thewill be displayed before the value range: data in that sheet will be a property of*Data Sheet*.*Total Sheet*

- Press
**Enter**.

The result is **1725**.

**Download Practice Workbook**

Download the free practice Excel template.

## Excel SUMIFS Function with Date Range: Knowledge Hub

**How to Use SUMIFS for Date Range with Multiple Criteria****How to SUMIF between Two Dates and with Another Criteria**

**<< Go Back to ****Excel SUMIFS Function** **|** **Excel Functions** **|** **Learn Excel**

amazing

Hello

Ava,Thanks