Table of Contents
SUMIFS is a function in Excel that allows you to sum a range of cells that meet multiple criteria. You can use SUMIFS to sum values based on a date range by specifying the start and end date as criteria. This makes it easier to quickly calculate the sum of values over a given period of time.
You can use the following syntax to calculate the sum of values in Excel where corresponding cells fall in a certain date range:
=SUMIFS(B2:B11, A2:A11, ">="&D2, A2:A11,"<="&E2)
This formula calculates the sum of the values in the range B2:B11 where the date in the range A2:A11 is between the dates in cells D2 and E2.
The following example shows how to use this syntax in practice.
Example: Use SUMIFS with Date Range in Excel
Suppose we have the following dataset in Excel that shows the number of sales made by some company on various days:
We can define a start and end date in cells D2 and E2 respectively, then use the following formula to calculate the sum of the sales where the dates fall between the start and end date:
=SUMIFS(B2:B11, A2:A11, ">="&D2, A2:A11,"<="&E2)
The following screenshot shows how to use this formula in practice:
We can see that a total of 73 sales were made between 1/10/2022 and 1/15/2022.
We can manually verify this by taking the sum of the sales between these dates:
- 1/12/2022: 28
- 1/14/2022: 30
- 1/15/2022: 15
Sum of sales = 28 + 30 + 15 = 73.
If we change either the start or end date, the formula will automatically update to calculate the sum of the cells within the new date range.
For example, suppose we change the start date to 1/1/2022:
We can see that a total of 181 sales were made between 1/1/2022 and 1/15/2022.
The following tutorials provide additional information on how to work with dates in Excel: