The SUMIFS function allows you to sum values in a range that satisfies multiple conditions. When it comes to working with dates, SUMIFS with date range can be particularly handy, especially when you need to sum values within a specific date range.
In this article, we will explore the following topics in detail –
Table of Contents
Let us look at each of these topics.
Download the Excel Workbook below to follow along and understand how to use SUMIFS with date range in Excel –
DOWNLOAD EXCEL WORKBOOK
Introduction to SUMIFS with date range
SUMIFS allows you to sum values in a range that meets multiple conditions or criteria. Whether you’re analyzing sales data, tracking project milestones, or monitoring expenses over time, SUMIFS can simplify your data calculations.
Its syntax is as follows:
=SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criteria_Range2,Criteria2…)
- Sum_Range (required) – The range of cells to sum.
- Criteria_Range1 (required) – The range that is tested using Criteria1. Criteria_range1 and Criteria1 set up a search pair whereby a range is searched for specific criteria. Once items in the range are found, their corresponding values in Sum_range are added.
- Criteria1 (required) – The criteria that define which cells in Criteria_range1 will be added. For example, criteria can be entered as 32, “>32”, B4, “apples”, or “32”.
- Criteria_Range2, Criteria2, … (optional) – Additional ranges and their associated criteria.
For SUMIFS with date range, we need to provide the start date and the end date as the two criteria. Let us look at a few examples to help us understand this function better.
Basic Example
Suppose you have dates in one column and the sales amount in another column, and you want to calculate the total sales for the month of January. This calculation can easily be done with the help of SUMIFS with date range.
STEP 1: Enter the SUMIFS function.
=SUMIFS(
STEP 2: Enter the first argument – Sum_range. Here we have selected the range B2:B30 as it contains all the sales figures.
=SUMIFS(B2:B30,
STEP 3: Enter the second argument – Criteria_range1. Here we have selected A2:A30 range as it contains the date of the sales transaction.
=SUMIFS(B2:B30,A2:A30,
STEP 4: Enter the third argument – Criteria1. Here we have entered the criteria as “>=”&1/1/2023.
=SUMIFS(B2:B30,A2:A30,”>=”&”1/1/2023″,
STEP 5: Enter the fourth argument – Criteria_range2. Here we have selected A2:A30 range as it contains the date of the sales transaction.
=SUMIFS(B2:B30,A2:A30,”>=”&”1/1/2023″,A2:A30,
STEP 6: Enter the fifth argument – Criteria2. Here we have entered “<=”&1/31/2023.
=SUMIFS(B2:B30,A2:A30,”>=”&”1/1/2023″,A2:A30,”<=”&”1/31/2023″)
As we can see, the SUMIFS function returns the sum of sales for the month of January, applying multiple criteria at once.
SUMIFS with dynamic date range
In many cases, you would want to create dynamic date ranges that automatically adjust based on the current date or other factors. This allows you to analyze data for the current month, quarter, or any other time frame without manually updating your formulas.
This can be achieved by the help of the formula – TODAY(). TODAY() function returns the current date and gets updated automatically each day, reflecting the current date.
Suppose you want to calculate the sum of sales for the last 7 days using the SUMIFS function. Follow the steps below to get the result –
STEP 1: Enter the SUMIFS function.
=SUMIFS(
STEP 2: Enter the first argument – Sum_range. Here we have selected the range B2:B30 as it contains all the sales figures.
=SUMIFS(B2:B30,
STEP 3: Enter the second argument – Criteria_range1. Here we have selected A2:A30 range as it contains the date of the sales transaction.
=SUMIFS(B2:B30,A2:A30,
STEP 4: Enter the third argument – Criteria1. Here we have entered the criteria as greater than 7 days minus today’s date i.e. “>”&TODAY()-7
=SUMIFS(B2:B30,A2:A30,”>”&TODAY()-7,
STEP 5: Enter the fourth argument – Criteria_range2. Here we have selected A2:A30 range as it contains the date of the sales transaction.
=SUMIFS(B2:B30,A2:A30,”>”&TODAY()-7,A2:A30,
STEP 6: Enter the fifth argument – Criteria2. Here we have entered the “>=”&TODAY() function.
=SUMIFS(B2:B30,A2:A30,”>”&TODAY()-7,A2:A30,”<=”&TODAY())
As we can see, the SUMIFS function returns the sum of sales for the last 7 days.
Conclusion
Mastering SUMIFS with date range in Excel opens up a world of possibilities for efficient data analysis. Whether you need to calculate totals for specific months or create dynamic date ranges that adapt to changing time frames, the SUMIFS function is a powerful tool in Excel.
Further learning:
Click here to learn all about SUMIFS in Excel.
