Md. Abdul Kader
While working with data sets that contain dates, you may have to sum the values based on the given date frequently. In this article, we’ll focus on the ways how we can sum values based on date in Excel with given criteria.
Download Practice Workbook
You can download the practice workbook from the link below.
4 Ways to Determine Sum Values Based on Date in Excel
Let’s look at our dataset prior to starting the main calculations. Here, product items are given with their order date and bill amount.
Now, we have to calculate the sum values of items based on the order date.
In this section, you will find 4 distinct ways to perform the task of finding Excel sum values based on date. Let’s get started.
1. Sum Values Based on a Date Range
If you want to knock out Excel sum values based on a date range i.e. between two dates, you may use two functions respectively: SUMIF function and SUMIFS function.
1.1. Using SUMIF Function
Firstly, we’ll see the use of the SUMIF function. The SUMIF function is an Excel function that helps to add all numbers to cells according to one criterion.
The syntax of the function is
=SUMIF (range, criteria, [sum_range])
For applying 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). 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], ...)
Regarding this, just input the following formula.
=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.
Firstly it will check whether the range1 (i.e, D5:D14) is greater than or equal to the considered Start Date (i.e. H5). After meeting this criterion, it again checks whether the range2 (i.e. D5:D14) is less than or equal to the considered End Date (i.e. I5). After meeting these two criteria,
SUMIFS(E5:E14,D5:D14,”>=”&H5,D5:D14,”<=”&I5) returns the final Output => 2450
2. Sum Values for Equal or Same Dates
Let’s imagine some dates are equal though items, bill amounts, and other information may differ.
In such a situation, we can use 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
So, the final Output => 600
3. Sum Values Based on Today’s Date
Assuming the situation where you have to calculate the sum values based on today’s date for all days either 10 days earlier from Today or 10 days later from Today. Here, I have given an example of 10 days earlier than today’s date. At first, apply the TODAY function to get today’s date.
=TODAY()
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
- H6 = 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 the dates which are 10 days earlier from today’s date 25-Oct-22 which are: 300, 500, 100, 500, 800, 250, 500
the final Output is got by summing all of them => 2950
4. Sum Values Based on Dates with Multiple Criteria
If you want to calculate the sum values for a specific item like Monitor in our data set based on a range of dates, you can insert the following formula.
=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.
From the start date (i.e. H5) and the end date (i.e. I5), the cell value of H6 (i.e. Monitor) was found 3 times => 300, 250, 350
So, the final Output => 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 (e.g. input as “<”)
- Be careful with the formula and apply correct logic (e.g. don’t input “>” instead of “>=”)
Conclusion
These are some effective methods following which you can easily calculate the Excel sum values based on date. Choose one according to your requirement. And, if you have any suggestions or confusion, please leave them below in the comments section.
You can check more articles on our website. Happy Excelling!
