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 SUMIFS between two dates in Google Sheets for varying purposes.
This Article Covers:
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])
Here,
- 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.
Sumifs Between Two Dates Instead of SUMIF
The main difference between the SUMIF and SUMIFS functions is that SUMIF lets you specify just one condition. SUMIFS, on the other hand, allows you to specify more than one condition. SUMIF between two dates simply wouldn’t work because it doesn’t account for enough arguments.
The Syntax for the Google Sheets SUMIFS With Date Function
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:
=SUMIFS(sum_range,criteria_range1,”>date1”, criteria_range2,”<date2”)
Using SUMIFS Date Range 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:
=SUMIFS(B2:B10,C2:C10,”>”&E4,C2:C10,”<”&F4)
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:
=SUMIFS(B2:B10,C2:C10,”>”&E4,C2:C10,”<”&TODAY())
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:
=SUMIFS(B2:B10,C2:C10,”>=”&E4,C2:C10,”<”&EOMONTH(E4,0)
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:
Google Sheets Sumifs Date Range Calculations 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:
=EOMONTH(TODAY(),-1)+1
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:
=EOMONTH(TODAY(),0)
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”)
What Might Be Going Wrong? (SUMIF With Date Range)
You may be running into issues if you are trying to use SUMIF between dates instead of SUMIFS. SUMIF with dates tends not to work as the formula is not capable of running enough arguments to account for a range of values.
How Do I Use SUMIFS With Date Ranges?
If you have the dates typed into your sheet, you can simply use the SUMIFS formula as normal with the syntax of =SUMIFS(sum_range,criteria_range1,”>date1”, criteria_range2,”<date2”) This indicates that the criteria_range1 (your date range) must be greater than date1, while it must also be less than date2. You can also use other functions to indicate dates, such as the TODAY() function.
How Do I Use SUMIFS by Month?
You can either add the dates for the end or beginning of months manually, or you can use functions such as EOMONTH() to automatically SUMIFS through the end of each month.
Final Word
In this tutorial, we showed different ways in which we can use SUMIFS between two dates in Google Sheets when you want to sum cell values between two dates in. We hope this was informative and useful for you. Check out our other guides to learn more about Google Sheets.
