How to Use The SUMIFS Function to SUM Values in a Date Range in Excel -11 Examples

The Excel SUMIFS Function

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

Generic Syntax:

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,

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 Product manufactured from 5/5/2019 to 5/10/2019:

Steps:

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

The result is 1602.

Formula Breakdown

The generic formula of SUMIFS is,

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

The formula used is:

• D5:D15 = the column range to sum the Amount values
• 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 Product “Jersey” only, from  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 Total Amount, H7, enter the following formula and press Enter.

The result is 579.

Formula Breakdown

The generic formula is,

The formula used is:

• D5:D15 = the column range to sum the Amount values
• 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 Total Amount, H7, enter the following formula and press Enter.

The result is 1035

Formula Breakdown

The formula used is:

which follows the syntax of:

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 Total Amount, H7, enter the following formula and press Enter.

The result is 1308.

Formula Breakdown

The generic formula of SUMIFS is,

The formula used is:

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) from the current date and extracts the sum values.
  • To calculate the values between the current date and the days ahead of it, add (+) the number of days with TODAY().

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 Total Amount, H7, enter the following formula and press Enter.

The result is 2887.

Formula Breakdown

The generic formula of the SUMIFS is,

The formula used is:

• 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 Product in November.

Steps:

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

The result is 2911.

Formula Breakdown

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

Syntax:

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

The generic formula of SUMIFS is,

The formula used is:

• The given month (November in 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 Total Amount, H6, enter the following formula and press Enter.

The result is 1197.

Formula Breakdown

The generic formula of the SUMIFS is,

The formula used is:

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 and the end of that month.

Steps:

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

The result is 1368.

Formula Breakdown

The generic formula of the SUMIFS is,

The formula used is:

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 (2019 in H4).
• In Total Amount, H5, enter the following formula and press Enter.

The result is 1725.

Formula Breakdown

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

Syntax:

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

The generic formula of the SUMIFS is,

The formula used is:

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 (11/10/2021 in I4).
• Store the End Date as 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.

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

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 Total Sheet), enter the formula.

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 the Data Sheet will be displayed before the value range: data in that sheet will be a property of 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