Table of Contents
The SUMIFS function is a useful tool in Google Sheets that allows users to calculate a sum based on a specific date range. This function requires several parameters, including the range of cells to sum, the criteria range(s) for the dates, and the date range itself. By using the SUMIFS function, users can easily filter and sum data within a specific date range, making it a valuable tool for financial analysis and budgeting. This function is especially useful for businesses and individuals who need to track and analyze data over a certain period of time. With its simple yet powerful formula, the SUMIFS function in Google Sheets provides an efficient and accurate solution for calculating sums based on a date range.
Use SUMIFS with a Date Range in Google Sheets
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.
Additional Resources
The following tutorials provide additional information on how to work with dates in Google Sheets:
Cite this article
stats writer (2024). How can I use the SUMIFS function to calculate a sum based on a date range in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-sumifs-function-to-calculate-a-sum-based-on-a-date-range-in-google-sheets/
stats writer. "How can I use the SUMIFS function to calculate a sum based on a date range in Google Sheets?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-sumifs-function-to-calculate-a-sum-based-on-a-date-range-in-google-sheets/.
stats writer. "How can I use the SUMIFS function to calculate a sum based on a date range in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-sumifs-function-to-calculate-a-sum-based-on-a-date-range-in-google-sheets/.
stats writer (2024) 'How can I use the SUMIFS function to calculate a sum based on a date range in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-sumifs-function-to-calculate-a-sum-based-on-a-date-range-in-google-sheets/.
[1] stats writer, "How can I use the SUMIFS function to calculate a sum based on a date range in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use the SUMIFS function to calculate a sum based on a date range in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.