Md. Sourov Hossain Mithun
Working in Excel, you may often need to sum numbers within specific dates. This article will provide you with 7 quick methods to apply the SUMIFS function with date range and multiple criteria.
So let’s have an overview of the methods we are going to use for serving our purpose with relevant output from the methods.
Download Practice Book
You can download the free Excel template from here and practice on your own.
7 Quick Methods to Use SUMIFS for Date Range with Multiple Criteria
Let’s get introduced to our dataset first. I have placed some salespersons’ names, dates, and sales in my dataset. Here, we want to find the total sales over the dates.
So, let’s check the 7 ways to use the SUMIFS function for date range with multiple criteria.
Method 1: Use SUMIFS Function to Sum Between Two Dates
I’ll use the SUMIFS function to find the total sales between two dates. The SUMIFS function in Excel is used to sum up the cells that meet multiple criteria.
Here, I’ll sum up the sales between the dates 1/10/2020 and 10/10/2020
Steps:
➥ Activate Cell C16
Type the formula given below:
=SUMIFS(D5:D12,C5:C12,">"&C14,C5:C12,"<"&C15)➥ Then just hit the ENTER button.
Now you will notice the expected result.
Read more: How to Use SUMIFS to SUM Values in Date Range in Excel
Method 2: Combination of SUMIFS and TODAY Functions to Enter a Date Range with Criteria
In this method, we’ll use the combination of the SUMIFS and TODAY functions to sum up the sales from today to any previous or after the date. The TODAY function returns the current date.
I’ll calculate here from today to the previous 5 days.
Steps:
➥ In Cell C14 type the given formula-
=SUMIFS(D5:D12,C5:C12,"<"&TODAY(),C5:C12,">"&TODAY()-5)➥ Press the ENTER button.
Now you will observe that we have got our results.
👇 Formula Breakdown
➥ TODAY()
The TODAY function will extract todays’ date. It will return as-
{11/31/2021}
➥ SUMIFS(D5:D12,C5:C12,”<“&TODAY(),C5:C12,”>”&TODAY()-5)
Then the SUMIFS function will calculate the sum between the date from the TODAY function and the previous 5 days. We subtracted 5 from the today function for that reason. That will result in as-
{15805}
Note: To calculate from today to after 5 days just type +5 in the formula.
Read More: Exclude Multiple Criteria in Same Column with SUMIFS Function
Method 3: SUMIFS Function to Sum Between Two Dates with Additional Criteria
We can sum up the sales between two date range with additional criteria too using the SUMIFS function. I’ll find the total sales value for “Bob” between the two dates.
Steps:
➥ Write the formula in Cell C16
=SUMIFS(D5:D12,C5:C12,">"&C14,C5:C12,"<"&C15,B5:B12,"*Bob*")➥ Click the ENTER button then.
Then you will spot that the sales value of Bob is calculated.
Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria
Similar Readings
- Excel Formula Date Range
- Excel SUMIF with a Date Range in Month & Year (4 Examples)
- How to Use VBA Sumifs with Multiple Criteria in Same Column
- SUMIFS with INDEX-MATCH Formula Including Multiple Criteria
- How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
Method 4: Use SUMIFS and DATE Functions Together to Sum with Multiple Criteria
Here, we’ll use another combination of functions- the SUMIFS function and the DATE function. The DATE function is used to return a serial number that corresponds to a date.
Steps:
➥ Type the formula in Cell C16:
=SUMIFS(D5:D12,C5:C12,">"&DATE(2020,1,10),C5:C12,"<"&DATE(2020,10,10))➥ Hit the ENTER button.
Now you will notice that our expected result is calculated.
👇 How Does the Formula Work
The DATE function will return a serial number that corresponds to the given date. DATE(2020,1,10) will return as-{43840} and DATE(2020,10,10) will return as-{44114}.
➥ SUMIFS(D5:D12,C5:C12,”>”&DATE(2020,1,10),C5:C12,”<“&DATE(2020,10,10))
Finally the SUMIFS function will sum up the sales value according to that date range and that will return as-
{22241}
Read More: How to Use SUMIFS Function in Excel with Multiple Criteria
Method 5: Insert SUMIFS and DATE Functions to Sum in a Specific Year
Here, we’ll use the previous methods’ functions again to sum up the sales for a specific year. I’ll calculate here for the year 2021.
Steps:
➥ Activating Cell C16 type the given formula-
=SUMIFS(D5:D12,C5:C12,">"&DATE(2021,1,1),C5:C12,"<"&DATE(2021,12,31))➥ Press the ENTER button then.
Then you will spot that the specific years’ sales value is summed up.
👇 How Does the Formula Work
This formula works like the previous method.
Read More: [Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)
Method 6: Combination of SUMIFS and EOMONTH Functions to Sum in a Specific Month
In this method, we’ll use the SUMIFS function and the EOMONTH function to sum up for a specific month. The EOMONTH function calculates the last day of the month after adding a specified number of months to a date. I’ll calculate here for the month “March”.
Step 1:
➥ Write the first date of March in Cell C14
Step 2:
➥ Then press that cell and click as follows- Home > Number > Arrow icon.
A dialog box named “Format Cells” will open up.
Step 3:
➥ Then press the Custom option.
➥ Write “mmmm” on the Type bar.
➥ Press Ok.
Then the cell will show the month name.
Step 4:
➥ Type the formula in Cell C15 as given below-
=SUMIFS(D5:D12,C5:C12,">="&C14,C5:C12,"<="&EOMONTH(C14,0))➥ Press the ENTER button now.
Now you will spot that our operation is done.
👇 How Does the Formula Work
➥ EOMONTH(C14,0)
The EOMONTH function will store the date as a sequential serial number so it can be used in the calculation. It will return as-
{43921}
➥ SUMIFS(D5:D12,C5:C12,”>=”&C14,C5:C12,”<=”&EOMONTH(C14,0))
Finally, the SUMIFS function will calculate the sales value according to that date range and that will return as-
{18480}
Read More: SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)
Method 7: Use SUMIFS Function to Sum Between a Date Range from Another Sheet
In our last method, I will show how to use the SUMIFS function to sum between a date range if the data is given in another sheet.
Please have a look that our data is in “Sheet1” but we will calculate in another sheet.
We’ll calculate in this sheet named “Another Sheet”.
Steps:
➥ In Cell C6 write the given formula:
=SUMIFS(Sheet1!D5:D12,Sheet1!C5:C12,">"&C4,Sheet1!C5:C12,"<"&C5)➥ Then just hit the ENTER button.
Please take a look now our calculation is done.
Read More: SUMIFS with Multiple Criteria in the Same Column (5 Ways)
Conclusion
I hope all of the methods described above will be good enough to use the SUMIFS function to sum within multiple criteria. Feel free to ask any questions in the comment section and please give me feedback.
Related Articles
- Calculate Date Range in Excel
- How to Do SUMIF Date Range Month in Excel (9 Ways)
- Excel SUMIFS with Multiple Vertical and Horizontal Criteria
- Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)
- How to Use SUMIFS When Cells Are Not Equal to Multiple Text
- SUMIFS with Multiple Criteria Along Column and Row in Excel
This is very helpful. I just have one question. When I try to do a sumifs with date ranges, I only get 0s. I figured out that it must be because I have a lot of data points with the same date. My rows are employees’ voids. I pulled the data for last year. Obviously, a lot of voids happen on a single date. I changed the dates to make them all different (even by just a day), and it works. What do I need to do to use this sumifs function for my purposes. Thank you
Greetings, Nat Troy. We don’t have a large enough dataset to test your problem. Please send us your file to [email protected]. So we can take a closer look at the issue.