Sum Values Based on Date in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


Sum Values Based on Date in Excel: 4 Ways

Let’s look at our dataset prior to starting the main calculations. Here, product items are given with their order date and bill amount.

Dataset to Calculate Excel Sum Values Based on Date

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])

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.

Excel Sum Values Based on Date

💥 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.

Excel Sum Values Based on Date Range

💥 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

Read More: How to Use SUMIF in Date Range and Month in Excel


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

Excel Sum values Based on Equal Date

💥 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. 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.

Excel Sum Values Based on Today's Date

💥 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

Read More: How to Do SUMIF by Month and Year in Excel


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 “>=”)

Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

These are some effective methods following which you can easily calculate the Excel sum values based on date. Choose one according to your requirements. 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!


<< Go Back to SUMIF Date Range | Excel SUMIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

2 Comments
  1. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo