Excel SUMIFS with Dates

SYNTAX: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], [criteria_range3, criteria3],…)

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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: SUMIFS with Dates (wallstreetmojo.com)

Examples

You can download this SUMIFS with Dates Excel Template here – SUMIFS with Dates Excel Template

Example #1

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.

You will use the SUMIFS functionSUMIFS FunctionThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. read more with dates to get the total sales.

  • Sum_range: Sum_range will be the sales column as you need to get the total sales.Criteria_range1: Criteria_range1 will be Range from the Category columnCriteria1: Criteria1 is Office Supplies as criteria_range1 is a “Category” column that contains the criteriaCriteria_range2: Criteria_range2 will be Range from the “Region” column.Criteria2: Criteria2 will be “West” as you need to get the sales for the West region.

Note: You can also use “Category” in criteria_range1 and “Region” in criteria_range2. However, we should select criteria according to criteria_range.

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

The answer is 2762.64.

Example #2

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.

The answer is 3695.2.

Example #3

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.

The answer is 966.14.

Example #4

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 OperatorExcel Logical OperatorLogical operators in excel are also known as the comparison operators and they are used to compare two or more values, the return output given by these operators are either true or false, we get true value when the conditions match the criteria and false as a result when the conditions do not match the criteria.read more “=”. Hence, this will include both dates.

The answer is 5796.2.

Example #5

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.

The answer is 1864.86.

Example #6

Question: 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,”<>”)

This article is a guide to SUMIFS with Dates. Here, we will learn how to sum values between two date ranges in Excel, practical examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • What is SUMIFS With Multiple Criteria?How to Sum Multiple Rows in Excel?SUM Shortcut in ExcelSUMIF With VLOOKUP Explanation