Here’s a sample dataset that will be used to explain the concept of sum values based on dates in Excel. It’s a simple sales table with the date of the sale and the bill amount alongside other information. The main manipulations will be done using the data from columns D and E.
Method 1 – Sum Values Based on a Date Range
If you want to calculate sum values based on a date range i.e. between two dates, you can use two functions: SUMIF function and SUMIFS function.
1.1. Using SUMIF Function
The SUMIF function is an Excel function that adds numbers from a range, if they abide by the criteria you set out.
The syntax of the function is
=SUMIF (range, criteria, [sum_range])
where the range is the range of cells that are being evaluated against the criteria, while the optional sum_range is the actual cells being summed (if you omit it, the range itself will be summed if it meets the criteria).
The criteria basically performs an IF function on each cell, but is expressed as a number or string that will be used in an implict IF function. If the criteria is a number or plain text, it will implicitly compare if the checked value matches it. Otherwise, the string value will be used to build an IF statement.
To apply this function, just insert the following formula in the Excel formula bar:
=SUMIF($D$5:$D$14,">="&$H$5,$E$5:$E$14)-SUMIF($D$5:$D$14,">"&$I$5,$E$5:$E$14)
Here,
- D5:D14 = the range of order date
- H5 = the start date
- E5 = the end date
- E5:E14 = the range of bill amount.
Formula Breakdown
SUMIF($D$5:$D$14,”>=”&$H$5,$E$5:$E$14) checks whether the range of Order Date (i.e. $D$5:$D$14) is greater or equal to the considered Start Date (i.e. H5). The & operand concatenates the “>=” sign and the value given into a string. As it meets the condition, it returns => 3830
Again, SUMIF($D$5:$D$14,”>”&$I$5,$E$5:$E$14) checks whether the range of Order Date (i.e. $D$5:$D$14) is greater than considered End Date (i.e. I5). As it meets the condition, it returns => 1380
So, final Output = 3830-1380 = 2450
As the SUMIF meets one criterion, we had to use the minus operator as shown in the previous screenshot.
1.2. Using SUMIFS Function
Let’s use the SUMIFS where we don’t need such complexity. The SUMIFS function is also an Excel built-in function that adds all arguments based on multiple criteria.
The syntax of the function is
=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)
It behaves similarly to the SUMIF function, but bear in mind that the sum_range must be explicitly provided and the order of the arguments is slightly changed.
The calculation formula is:
=SUMIFS(E5:E14,D5:D14,">="&H5,D5:D14,"<="&I5)
In this formula,
- E5:E14 = the range of bill amount
- H5 = the start date
- D5:D14 = the range of order date
- I5 = the end date.
Formula Breakdown
Here, the function will sum values from the Sum_Range E5:E14.
For each cell in the sum_range, the function checks whether the appropriate cell from ange1 (i.e, D5:D14) is greater than or equal to the considered Start Date (i.e. H5). After meeting this criterion, it again checks the appropriate cell from range2 (i.e. D5:D14) is less than or equal to the considered End Date (i.e. I5). After meeting these two criteria, the cell’s value is added to the sum.
Read More: How to Use SUMIF in Date Range and Month in Excel
Method 2 – Sum Values for Equal or Same Dates
If you need to only sum values that have a date equal to the provided value, it results in a simplified version of the previous problem, where you only need to use a single check. You can use either SUMIF or SUMIFS function to solve it.
Consider the following formula to find the sum bill amount.
=SUMIFS(E5:E14,D5:D14,DATE(2021,6,7))
Here,
- E5:E14 = the range of bill amount
- D5:D14 = the range of order date
- 2021,6,7 (year, month, day) = the equal date i.e. 7-Jun-21
Formula Breakdown
Here, the function will sum values from the Sum_Range E5:E14.
DATE(2021,6,7) means 07-June-2021
The SUMIF function finds 07-June-2021 in the Criteria_Range D5:D14 and finds two values in the Sum_Range: $500 & $100
Therefore, the sum will result in 600
In this example, you can also replace the explicit date provided in the formula with H5.
Method 3 – Sum Values Based on Today’s Date
Suppose you need to calculate the value before the past 10 days including today. First, you can apply the TODAY function to get today’s date and put it in the cell H4 that will be used as a starting point.
=TODAY()
Then, the “Earlier Days” cell (H5) can contain a number of days to “go back” and calculate the sum for.
To find the sum of all bill amounts, just enter the following formula:
=SUMIF(D5:D14,"<="&H4-H5,E5:E14)
Here,
- E5:E14 = the range of bill amount
- D5:D14 = the range of order date
- H4 = the current date
- H5 = the number of earlier days.
Formula Breakdown
Here, the function will sum values from the Sum_Range E5:E14.
The function calculates the sum for cells that have dates at least 10 days earlier than today’s date (provided in the practice sheet as Oct 26, 2022). The function will subtract 10 days from the date (because H5 is 10), with Excel automatically applying the conversion from the result back into date form, then determine whether each cell in the range D5:D14 has a lower value than the result of H4 – H5.
To calculate the sum for dates in the past 10 days from today, you can shift the “<=” in the formula into “>=”, but this only works if the table doesn’t contain future dates.
Read More: How to Do SUMIF by Month and Year in Excel
Method 4 – Sum Values Based on Dates with Multiple Criteria
As soon as you need a different criteria apart from the date, you need to use the SUMIFS formula. Let’s say you want to calculate the total Monitor sales between Jun 1 and Jul 25, 2021. The criteria are provided in cells H5, H6, and I5 (start and end dates to use, and a matching item name). Here’s the formula you’d need to use.
=SUMIFS($E$5:$E$14,$D$5:$D$14,">="&$H$5,$D$5:$D$14,"<="&$I$5,$C$5:$C$14, $H$6)
In this formula,
- E5:E14 = the range of bill amount
- D5:D14 = the range of order date
- H5 = the start date
- I5 = the end date
- C5:C14 = the range of item name
- H6 = the name of a specific item.
Formula Breakdown
Here, the function will sum values from the Sum_Range E5:E14.
The function will check whether the date in column D is higher than the start date (i.e. H5), but lower than the end date (i.e. I5), and then try to match the value from column C with the cell value of H6 (i.e. Monitor). It will find three results and sum three values from column E => 300, 250, 350
So, the final Output becomes => 300+250+350 => 900
Things to Keep in Mind
- See the dates and figures format (use Format Cells if any changes are happened automatically by the Excel).
- Don’t forget to use double quotes when creating logic criteria for SUMIF and SUMIFS (e.g. input as “<”).
- Be careful with the formula and apply correct logic (e.g. don’t input “>” instead of “>=”).
- The TODAY() value automatically updates, so it will yield different results later.
Download Practice Workbook
You can download the practice workbook from the link below.
<< Go Back to SUMIF Date Range | Excel SUMIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hello, I have what may be an impossible situation….. I capture values on a given day (from one worksheet) and that works fine but, the next day the values may or may not change and I need to maintain the values of the previous day until months end. Currently my captured value(s) move to the current date and are not captured and stay on the previous date. I can’t figure out how to capture a values on 9/3/23 and have those values remain there even though it is now 9/4/23. My captured values moved to 9/4/23 but there aren’t actually any values to capture for 9/4/23. New values will occur on 9/5 replacing the current values but I don’t want to lose the 9/4 values……
Hello Terry Smith,
Thanks for your question. Most probably you are using a function like TODAY() function to find the first date in our worksheet. Then you have applied the Fill Handle feature. That’s why, the days are updating every day. The solution to this problem is to manually input the first date and then apply the Fill Handle feature.
Also, if your problem is different from the problem that I have assumed, then give us the Excel files. It will be convenient for us to solve the issue.