Sum Values Based on Date in Excel (4 Ways)

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.

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

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.

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


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

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


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!


Further Readings

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo