The SUMIFS function in Google Sheets is quite helpful in a variety of situations, including those where you want to find the sum of cell values within a given date range. What this means is, we can use the SUMIFS function to find the sum of values in a column, if corresponding dates in another column fall between two given dates.
In this tutorial we will explain how to do just that. We will also show you different ways to use the Google Sheets SUMIFS function with dates in different situations.
What Does the SUMIFS Function Do?
The SUMIFS function is used to find the sum of a range of values that satisfy a given set of conditions. It essentially scans through the range of cells, retrieves values of those that match all the given conditions and then sums up these retrieved values.
Syntax for the SUMIFS Function
The syntax for the SUMIFs function is as follows:
=SUMIFS(sum_range, criteria_range1, criteria1,[ criteria_range2, criteria2, ... criteria_range_n, criterian])
- sum_range is the range of cells containing the values you want to test.
- criteria_range1 is the range you want to check for criteria1.
- criteria1 is the condition that criteria_range1 should satisfy.
- criteria_range2, criteria2, etc. are additional ranges and criteria for checking (if needed).
As you can see from the syntax, you can add as many criteria as you need to.
However, it goes without saying that there needs to be at least 1 criteria_range and criteria parameter in the function.
Why Do We Use SUMIFS Instead of SUMIF to Sum Cell Values Between Two Dates?
Syntax for the Google Sheets SUMIFS with Date
Since we want to consider cell values between a pair of dates (say date1 and date2), we need to basically specify two conditions:
- That the date should come after the given date1
- That the date should come before the given date2
As such, the syntax of the SUMIFS function, for our situation, can be narrowed down to the following:
Using SUMIFS to Sum Cell Values Between Two Dates in Google Sheets
Consider the following dataset, containing dates and number of units (of a hypothetical product) sold.
Let’s say you want to find the total units sold between the dates mentioned in cells E4 and F4. You can find this amount by applying the SUMIFS function as follows:
Here’s the result we get when we apply the above formula to our data:
Using SUMIFS to Sum Cell Values Between a Given Date and Current Date
Let us take a look at a special case, where we want to find the total units sold starting from a given date up to the current date (today).
For this, you can use a DATE function like TODAY within the criteria parameters as follows:
The TODAY function simply returns the current date when the Google Sheet is opened. So, here’s what you would get if you opened the sheet on 7/18/2021.
Using SUMIFS to Sum Cell Values From a Given Date to the End of the Month
Another useful DATE function is the EOMONTH function, which returns the last day of the month for a given date.
Let us look at another special case, where we want to find the total units sold starting from a given date up to the end of that month. You can use the EOMONTH function in this case as follows:
The first parameter of EOMONTH specifies the date for which you want the last day of the month. The second parameter specifies the number of months after the given date, for which we want the last day of the month.
Since we want the last day of the same month, we specified the second parameter of the EOMONTH function as 0.
Here’s the result we get when this formula is applied to the data:
Using SUMIFS to Sum Cell Values for the Current Month
Finally, let’s look at a case where we want to find the sum of cell values for the entire current month. This will involve the use of both the TODAY and the EOMONTH functions in the SUMIFS criteria parameters.
We will need to find both the first day as well as the last day of the current month, because we need both of these in the criteria parameters of our SUMIFS function.
To find the first day of the current month, we use the formula:
Here, the first parameter of the EOMONTH function is the TODAY function, which returns the current date. The second parameter is specified as -1. So EOMONTH(TODAY(),-1) will give us the last day of the month that falls exactly one month before TODAY. In other words, the last day of last month.
Now, the first day of this month obviously falls one day after that, so we simply add a 1 to the date returned by this function.
Now we need to find the last day of the current month. For this, we use the formula:
We can now put both these formulae together to obtain the sum of cell values between the first and last days of the current month, as follows:
=SUMIFS(B2:B10,C2:C10,”>=”& EOMONTH(TODAY(),-1)+1,C2:C10,”<=”& EOMONTH(TODAY(),0))
Here’s the result we get when the formula is applied to the sample data:
We can further customize this formula to sum values between the given two dates, that also satisfy some additional criteria. This can be done by simply adding each criterion as parameters to the SUMIFS function.
So if we wanted to find the sum of units sold for the current month, for only carpets, we would write the formula as:
=SUMIFS(B2:B10,C2:C10,”>=”& EOMONTH(TODAY(),-1)+1,C2:C10,”<=”& EOMONTH(TODAY(),0),A2:A10,”Carpet”)
In this tutorial we showed different ways in which we can use the Google Sheets SUMIFS date function with dates when you want to sum cell values between two dates in Google Sheets. We hope this was informative and useful for you.