When working with data that contains dates, you may often need to sum values within a specific period. For example, you may want to calculate the total sales, revenue, or profit for a certain month, quarter, or year. Or you may want to compare the performance of different products, regions, or categories over time.
One way to do this in Excel is to use the SUMIFS function, which allows you to sum a range of values based on one or more criteria. In this article, we will show you how to use the SUMIFS function to sum values between two dates, and how to handle some common scenarios and challenges.
The SUMIFS function has the following syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Where:
- sum_range is the range of cells that contains the values to sum.
- criteria_range1 is the range of cells that contains the first criteria.
- criteria1 is the first condition that the cells in criteria_range1 must meet.
- criteria_range2, criteria2, … are optional additional ranges and conditions that the cells must meet.
To sum values between two dates, you need to use two criteria: one for the start date and one for the end date. The criteria must be logical expressions that compare the dates in the criteria range with the dates you specify. For example, to sum values between January 1, 2020 and January 31, 2020, you can use the following criteria:
- “>=1/1/2020” for the start date
- “<=1/31/2020” for the end date
Note that you need to enclose the operators (>= and <=) in double quotes, and concatenate them with the dates using the ampersand (&) operator. Alternatively, you can use cell references that contain the dates, such as F1 and F2, and write the criteria as “>=”&F1 and “<=”&F2.
Procedures
To use the SUMIFS function to sum values between two dates, follow these steps:
- Identify the range of cells that contains the values to sum, and enter it as the sum_range argument in the SUMIFS function. For example, if the values are in column C, you can enter C2:C100 as the sum_range.
- Identify the range of cells that contains the dates, and enter it as the criteria_range1 argument in the SUMIFS function. For example, if the dates are in column A, you can enter A2:A100 as the criteria_range1.
- Enter the criteria for the start date as the criteria1 argument in the SUMIFS function. You can either type the date directly, such as “>=1/1/2020”, or use a cell reference that contains the date, such as “>=”&F1.
- Enter the same range of cells that contains the dates as the criteria_range2 argument in the SUMIFS function. For example, you can enter A2:A100 again as the criteria_range2.
- Enter the criteria for the end date as the criteria2 argument in the SUMIFS function. You can either type the date directly, such as “<=1/31/2020”, or use a cell reference that contains the date, such as “<=”&F2.
- Press Enter to complete the formula and get the result.
Explanation
To illustrate how the SUMIFS function works, let’s look at an example with some sample data. Suppose you have a table that shows the sales of different products in different regions for each day of January 2020, as shown below:
You want to calculate the total sales for the product Printer in the region East between January 10, 2020 and January 20, 2020. To do this, you can use the following formula:
=SUMIFS(C2:C32,A2:A32,”>=1/10/2020″,A2:A32,”<=1/20/2020″,B2:B32,”Printer”,D2:D32,”East”)
This formula sums the values in column C (Sales) only if the following conditions are met:
- The corresponding date in column A is greater than or equal to January 10, 2020
- The corresponding date in column A is less than or equal to January 20, 2020
- The corresponding product in column B is Printer
- The corresponding region in column D is East
The result is $2,400, as shown below:
Note that you can also use cell references instead of typing the dates and criteria directly in the formula. For example, you can enter the start date in cell F1, the end date in cell F2, the product in cell F3, and the region in cell F4, and then use the following formula:
=SUMIFS(C2:C32,A2:A32,”>=”&F1,A2:A32,”<=”&F2,B2:B32,F3,D2:D32,F4)
This will give you the same result, but it will also allow you to change the criteria easily by changing the values in cells F1:F4.
Example
Let’s look at another example with some real data. Suppose you have a table that shows the daily revenue of a company for the year 2020, as shown below:
You want to calculate the total revenue for each quarter of the year. To do this, you can use the SUMIFS function with the following criteria:
- For Q1, the start date is January 1, 2020 and the end date is March 31, 2020
- For Q2, the start date is April 1, 2020 and the end date is June 30, 2020
- For Q3, the start date is July 1, 2020 and the end date is September 30, 2020
- For Q4, the start date is October 1, 2020 and the end date is December 31, 2020
You can enter the start and end dates for each quarter in cells F1:G4, and then use the following formula in cell H1 and copy it down to cell H4:
=SUMIFS(B2:B367,A2:A367,”>=”&F1,A2:A367,”<=”&G1)
This formula sums the values in column B (Revenue) only if the corresponding date in column A is between the start and end dates in columns F and G.
The result is shown below:
As you can see, the total revenue for Q1 is $1,234,567, for Q2 is $1,345,678, for Q3 is $1,456,789, and for Q4 is $1,567,890.
Other Approaches
The SUMIFS function is not the only way to sum values between two dates in Excel. Here are some other approaches that you can use:
- Use the SUMIF function with two criteria ranges and two criteria. The SUMIF function has a similar syntax to the SUMIFS function, but it only allows one sum range and one criteria range. However, you can use it twice with different criteria and subtract the results. For example, to sum values between January 10, 2020 and January 20, 2020, you can use this formula:
=SUMIF(A2:A32,”>=1/10/2020″,C2:C32)-SUMIF(A2:A32,”>1/20/2020″,C2:C32)
This formula first sums the values in column C that are greater than or equal to January 10, 2020, and then subtracts the values that are greater than January 20, 2020.
- Use the SUM function with an array formula. The SUM function can also sum values based on criteria, but you need to enter it as an array formula by pressing Ctrl + Shift + Enter. For example, to sum values between January 10, 2020 and January 20, 2020, you can use this formula:
=SUM((A2:A32>=DATE(2020,1,10))*(A2:A32<=DATE(2020,1,20))*C2:C32)
This formula uses the DATE function to create the start and end dates, and then multiplies them with the date range and the sum range. The multiplication acts as a logical AND operation, and returns an array of values that meet both criteria. The SUM function then adds up the values in the array.
- Use a pivot table. A pivot table is a powerful tool that can summarize and analyze data in various ways. To use a pivot table to sum values between two dates, follow these steps:
- Select the data range that contains the dates and values, and go to the Insert tab > PivotTable.
- In the Create PivotTable dialog box, choose where you want to place the pivot table, and click OK.
- In the PivotTable Fields pane, drag the date field to the Rows area, and the value field to the Values area.
- Right-click on any date in the pivot table, and choose Group.
- In the Grouping dialog box, choose the grouping level you want, such as Months, Quarters, or Years, and click OK
