# SUMIFS With Dates

Published on :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya

## What Is Excel SUMIFS With Dates?

The SUMIFS function is used when there are more than one criteria; when fulfilled, the range of cells is summed. This function also supports dates as the criteria and the operators for the criterion.

Suppose someone asks you, “What are the total sales for Office Supplies in the West Region? Here, we have two criteria: 1 – “Office Supplies” from “Category” and 2 – “West” from the “Region” column.

The actual formula will be =SUMIFS(J2:J51,G2:G51,” Office Supplies”,E2:E51,” West”)

• SUMIFS with Excel function helps users obtain the sum with criteria, including dates.
• The syntax of SUMIFS with dates is =SUMIFS( Sum range, Range for Date, Criteria Date, Range for Date 2, Criteria Date 2) where all the arguments are mandatory.
• SUM_range is the cell range used to evaluate based on specified criteria.
• Criteria_range1 and criteria1 denotes the range of cells specifying 1st criteria and the first criterion to be specified from criteria_range1, respectively.
• Similarly, criteria_range2 and criteria2 denotes the range of cells specifying 2nd criteria and the first criterion to be specified from criteria_range2, respectively.

### Syntax

SYNTAX: =SUMIFS(sum_range, criteria_range1, criteria1, , ,…)

Arguments of Syntax:

• SUM_range: Sum_range is the range of cells you want to evaluate based on specified criteria.
• Criteria_range1: criteria_range1 is the range of cells from where you would like to specify 1st criteria.
• Criteria1: Criteria 1 is the first criterion that needs to be specified from criteria_range1.
• Criteria_range2: If you look at the syntax, criteria_range2 seems optional. The range of cells from which you would like to specify the second criteria.
• Criteria2: Criteria 2 is the second criterion that needs to be specified from criteria_range2.

### How To Use SUMIFS With Dates?

We will learn how to use SUMIFS with the following example.

Question: What is the total sales value of the “West” region before 2017?

Solution: Apply formula =SUMIFS(J2:J51,E2:E51,” West,” B2:B51,” <1/1/2017″), and it will give you the total sales value of “Orders Date” before 1/1/2017 for the “West” region.

Likewise, we can use SUMIFS with dates in Excel. For better understanding, we will use the same data with different years in the following examples.

### Examples

#### Example #1

Question: What is the total sales value of “Phones” (Sub-Category) from the “West” region before 8/27/2017?

Solution: Here, you need to get sales based on three conditions: – “Sub-Category,” “Region,” and “Order Date.” Here you need to pass criteria_range3 and criteria3 as well.

Apply formula =SUMIFS(J2:J51,E2:E51,” West”,H2:H51,” Phones”,B2:B51,” < 8/27/2017″). It will give you the total sales value of “Phones” (Sub-Category) from the “West” region before 8/27/2017.

#### Example #2

Question: What is the total sales value of orders in 2017?

Solution: You can see that here you need to get the sales value of a given year. However, you do not have year fields in the data. Instead, you only have "Order Date," so you can pass two conditions, 1st – "Order Date" is greater than or equal to 1/1/2017, and 2nd condition – "Order Date "is less than or equal to 12/31/2017.

Here, you need to apply the following SUMIFS formula for dates.

=SUMIFS(J2:J51,B2:B51,”>=1/1/2017″,B2:B51,”<=12/31/2017″)

As you can see, we have also passed excel logical Operator “=”. Hence, this will include both dates.

#### Example #3

Question: Someone asks you to get the sales value for all those orders where we did not have a customer name who purchased our product.

Solution: You can see several orders where the customer name is blank.

Here, you need to apply the following SUMIFS formula for dates =SUMIFS(J2:J51, C2:C51,”)

This formula will calculate only those cells with no value in the customer name field. If you pass “” (space in between the inverted comma), this will not give accurate results as space itself is considered a character.

### Important Things To Note

• SUMIF is an useful function as sometimes, we don’t have to calculate the total of all data but some specific data.
• During such times, SUMIF is useful.
• Similarly, when dates are also a criteria, we can use SUMIFS with dates function.

1. What is SUMIFS with dates?

SUMIF formula helps users obtain the total of data based on criteria. Similarly, SUMIFS with dates helps users find the total of data with dates as the criteria.

2. Explain the use of SUMIFS with dates with an example.

Someone asks you to get the sales value for all those orders where the customer name is not blank.

Here you need to apply the following SUMIFS formula for dates =SUMIFS(J2:J51,C2:C51,”<>”)

3. What is the difference between criteria1 and criteriarange1 in SUMIFS formula?

The syntax of SUMIFS with dates are =SUMIFS(sum_range, criteria_range1, criteria1, …)

The major difference is that criteria_range1 is the cell range that specifies 1st criteria and criteria1 the first criterion to be specified from criteria_range1.