You can use the following syntax to sum values in a cell range in Google Sheets that fall in a specific date range:
This formula takes the sum of the values in the range B2:B11 where the start date in A2:A11 is equal to or greater than the date in cell D2 and the end date is equal to or less than the date in cell E2.
The following example shows how to use this syntax in practice.
Example: Use SUMIFS with Date Range in Google Sheets
Suppose we have the following dataset in Google Sheets that shows the number of products sold 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 products sold between these two dates:
The following screenshot shows how to use this formula in practice:
We can see that a total of 73 products were sold between 1/10/2021 and 1/15/2021.
We can manually verify that this number of products were sold during the three dates in column A that fall within the date range:
- 1/12/2021: 28 products
- 1/14/2021: 30 products
- 1/15/2021: 15 products
Total products sold: 28 + 30 + 15 = 73.
If we change either the start or end date, the formula will automatically update to count the cells within the new date range.
For example, suppose we change the start date to 1/1/2021:
We can see that a total of 181 products were sold between 1/1/2021 and 1/15/2021.
The following tutorials provide additional information on how to work with dates in Google Sheets:
How to AutoFill Dates in Google Sheets
How to Add & Subtract Days in Google Sheets
How to Use COUNTIFS with a Date Range in Google Sheets
How to Calculate the Difference Between Two Dates in Google Sheets