Table of Contents
The SUMIFS function in Google Sheets can be used to easily find the sum of values within a date range. The function requires two or more range parameters, the criteria range and the criteria itself, along with the sum range. The criteria should be set to a date range in the format of “>=start date” and “<=end date”. This will allow you to quickly calculate the sum of values within a specified date range.
You can use the following syntax to sum values in a cell range in Google Sheets that fall in a specific date range:
=SUMIFS(B2:B11,A2:A11,">="&D2,A2:A11,"<="&E2)
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:
=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 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: