Md Shamse Tabrej
Sometimes you need to add up numbers in Excel that fall within a date range. To achieve this, we often apply SUMIFS date range multiple criteria A few simple approaches showing how to apply SUMIFS date range multiple criteria here.
📁 Download Excel File
Download the free practice book from here.
Learn to SUMIFS in Date Range with Multiple Criteria with These 7 ways
For instance, let’s consider our dataset. In my data set, I have included information about some Merchants including their names, dates, and sales profits.
Now I will demonstrate to you 7 ways you can apply to calculate the sum by using SUMIFS date range multiple criteria.
1. Use SUMIFS Function to Calculate Sum Between Two Dates
Using this method, I’ll calculate the total dates from November 1 through November 30, 2022.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell D19.
- Insert the following formula:
=SUMIFS(D6:D15,C6:C15,”>”&G5,C6:C15,”<“&G6)
- Press Enter to see the result.
Thus, we can calculate the sum between the two dates.
🔨 Formula Breakdown
👉 Range = D6:D15 : This is the chosen Cell Range where the SUMIF function will be implemented.
👉 Criteria 1 = C6:C15,”>”&C17 : Here, the function will determine whether cells in the range C6:C15 have values higher than cell G5.
👉 Criteria 2 = C6:C15,”<“&C18 : Here, the function will check whether the Cells in the C6:C15 range value are less than G6.
Read More: 9 Easy Ways to Calculate Date Range in Excel
2. Calculating Sum of N Previous or N After Days from Today
In this approach, we will add up all of the profit that the company made between today and 10 days ago using the SUMIF function and the Today function.
⬇️⬇️ STEPS ⬇️⬇️
- Pick cell D17.
- Write the formula below:
=SUMIFS(D6:D15,C6:C15,”<“&TODAY(),C6:C15,”>”&TODAY()-15)
- Finally, press Enter to see the output.
In this way, using the SUMIF and TODAY functions in conjunction, we can calculate the total profit.
🔨 Formula Breakdown
👉 Range = D6:D15 : The SUMIF function will be used in this selected Cell Range.
👉 Criteria = TODAY() : this function denotes the date of today.
👉 Criteria = C6:C15,”<“&TODAY() : will filter the date which is less than the date of today.
👉 Criteria = C6:C15,”>”&TODAY()-15 : will filter the date which is greater than the date of 15 days ago.
3. Calculating the Sum Between Two Dates Including Criteria with Excel SUMIFS Function
The SUMIFS function allows us to calculate the total sales for a given date period that meets multiple criteria. Between the 1st of November and the 30th of November, we will calculate David’s total profit.
⬇️⬇️ STEPS ⬇️⬇️
- Now select the blank cell D19.
- The input the formula:
=SUMIFS(D6:D15,C6:C15,”>”&G5,C6:C15,”<“&G6,’1′!B6:B15,”*David*”)
- Lastly, hit Enter to see the outcome.
This allows you to compute David’s total profit between two dates.
🔨 Formula Breakdown
👉 Range = D6:D15 : The SUMIF function will be used in this selected Cell Range.
👉 Criteria = C6:C15,”>”&G5 : Here the criteria will separate the value in C6:C15 range which is grater than G5.
👉 Criteria = C6:C15,”<“&G6 : the criteria will work same as Criteria 1.
👉 Criteria = “*David*” : this criteria will filter the value for David.
4. Sum Between Two Dates with Multiple Criteria Using SUMIFS and DATE Functions Combined
You can get a unique number that represents a date by using the DATE function. We will use both the DATE and SUMIFS functions to calculate the total profit in a given period.
⬇️⬇️ STEPS ⬇️⬇️
- Select the empty cell D19.
- Insert the following formula:
=SUMIFS(D6:D15,C6:C15,”>”&DATE(2022,11,1),C6:C15,”<“&DATE(2022,11,30))
- Hit Enter key to see the total.
🔨 Formula Breakdown
👉 Range = D6:D15 : The SUMIF function will be used in this selected Cell Range.
👉 Criteria = DATE(2022,11,1) : denotes a date.
👉 Criteria = C6:C15,”>”&DATE(2022,11,1) : will filter dates greater than it.
By doing so, you can sum up the total for November.
5. Calculate Sum of a Specific Year Using SUMIFS and DATE Functions
Here, we’ll add up the profits for a particular year using the same calculations as before. This is where I do the sum for the year 2022 later.
⬇️⬇️ STEPS ⬇️⬇️
- First, select empty cell D19.
- Later insert the following formula:
=SUMIFS(D6:D15,C6:C15,”>”&DATE(D2022,1,1),C6:C15,”<“&DATE(2022,12,31))
- In the end, hit Enter button to view the total.
🔨 Formula Breakdown
👉 Range = D6:D15: This is the selected Cell Range for use with the SUMIF function.
👉 Criteria = DATE(2022,11,1) returns a date.
👉 Criteria = C6:C15,”>”&DATE(2022,11,1) will filter dates greater than it.
Overall You can calculate the grand total for 2022 this way.
6. Calculate Sum of a Specific Month Using SUMIFS and EOMONTH Functions
In this approach, we will total for a given month using both the SUMIFS and EOMONTH functions. However, by adding a given number of months to a given date, the EOMONTH function determines that date’s corresponding status as the last day of the month. Here is my monthly calculation.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, fill in cell D17 on 1st November.
- And we will change the number format of the cell to Custom.
- And we will type ‘mmmm’ on the custom bar and select.
- Then select cell D18.
- Insert the following formula:
=SUMIFS(D6:D15,C6:C15,”>=”&G5,C6:C15,”<=”&EOMONTH(G5,0))
- Finally, press Enter so that you can see the result.
You can calculate the total of a particular month in this way.
🔨 Formula Breakdown
👉 Range = D6:D15: This is the selected Cell Range for use with the SUMIF function.
👉 Criteria = EOMONTH(G5,0) returns a month which belongs to cell G5.
Read More: 11 Examples to Do SUMIF in Date Range of Month and Year
7. Sum Between a Date Range of Another Sheet Using SUMIFS Function
Finally, if the data is provided in a separate sheet, we will demonstrate how to use the SUMIFS function to calculate the sum over a specified time period.
⬇️⬇️ STEPS ⬇️⬇️
- Now we will calculate the sum from the Dataset worksheet.
- Following that insert the following formula:
=SUMIFS(‘Data Set’!D6:D15,’Data Set’!C6:C15,”>”&’7′!C5,’Data Set’!C6:C15,”<“&’7′!C6)
- Finally, press Enter to see the total.
So we can calculate the total profit from another sheet.
🔨 Formula Breakdown
👉 Range = ‘Data Set’!D6:D15: This represents the specific Cell Range for use with the SUMIF function from sheet Dataset.
👉 Criteria = ‘Data Set’!C6:C15,”>”&’7′!G5:Here, the function will determine whether cells in the range C6:C18 have values higher than cell G5.
What to Do When SUMIFS Between Dates Is Not Working in Excel?
If your formula is giving you unexpected results or isn’t working at all, so the following troubleshooting advice may help you figure out why.
- Make Sure That Dates and Numbers are Formatted Correctly
Check that your dates are indeed dates and not just text strings that look like dates if a seemingly accurate SUMIFS formula outputs nothing but zero. Next, double-check that you’re adding actual numbers and not just text values. You can diagnose and repair these problems with the assistance of the following guides.
- Put Criteria in Their Proper Format
When using SUMIFS’s date function, you must put a date in quotes, like “>=9/10/2020” Cell references and functions, like “=”&G1 or “=”&TODAY, must be put outside the quotes.
- Check the Formula’s Reasoning
However, a budget error could have devastating consequences. Several hours of debugging time could be wasted due to a single typo in a formula in addition. Moreover, make sure that the beginning date is preceded by the larger than (>) or amounting to (>=) function and the ending date is preceded by the less than () or below or equal to (=) operator when performing a total between 2 dates.
- Achieve Uniformity in Range Sizes
To avoid a #VALUE! error while using the SUMIFS function, the sum range and the criteria range must be of similar size. As a workaround, you should check that the sum range and each of the criteria range arguments have an identical amount of rows and columns.
In Excel, the SUMIFS function is used to compile information from a specified period, to sum up. Please let me know in the comments section if you have any other creative suggestions for how to solve this problem. Please come back to our blog next week; we appreciate your time.
📄 Important Notes
🖊️ Frequently verify that you’ve employed Absolute Reference when it was required.
🖊️ Always look for ways that are easy to use in different situations.
🖊️ And most importantly, Try to use the shortcut key.
📝 Takeaways from This Article
📌 In this process, You can calculate the sum between two dates
📌 Moreover, You will be able to mix additional criteria under the SUMIFS function.
📌 Also, You can calculate the sum in a specific year.
📌 In addition, You can calculate the sum in a specific month.
📌 Finally, You will be able to calculate the sum from another sheet in your current worksheet.
Conclusion
In conclusion, I’m crossing my fingers that the aforementioned techniques for utilizing the SUMIFS function to sum inside several criteria will be adequate. Please use the comments area to ask me questions or provide feedback. Finally, to read more Excel-related content, head on over to ExcelDen.com.
