Sum Values Based on Date in Excel

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 determine the sum values within a specific date based on criteria also.

Download Practice Workbook

Basics Before Calculating Sum Values Based on Date

Calculating sum values depending on dates isn’t a complex task. But if you have some fundamentals related to the topic, this will make it easier to understand clearly.

What are SUMIF, SUMIFS & DATE Functions

The SUMIF function is an Excel function that helps to add all numbers to cells according to one criterion.

This function is available from earlier versions of Excel (e.g. Excel 2003) to the updated versions (e.g. Microsoft 365).

The syntax of the function is

=SUMIF (range, criteria, [sum_range])

There are the following arguments in the function.

range – The range of cells that you want to apply the criteria against.

criteria – The criteria used to determine which cells to add.

sum_range – [optional] The cells to add together.

On the other hand, the SUMIFS function is also a built-in Excel function that adds all arguments based on multiple criteria. This function was introduced in 2007. From its initiation, it’s becoming popular day by day.

The syntax of the function is

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

There are the following arguments in the function.

sum_range – The range to be summed.

range1 – The first range to evaluate.

criteria1 – The criteria to use on range1.

range2 – [optional] The second range to evaluate.

criteria2 – [optional] The criteria to use on range2

Besides, the DATE function returns the serial number that a particular date holds.

The syntax of the function is

=DATE (year, month, day) 

The arguments of the function are the following

year – Number for the year.

month – Number for the month.

day – Number for the day.

How to Enter Formula in Excel

Entering a formula in the Excel formula bar is quite a simple task. First, you have to select a blank cell where you want to show the output. Then input an equal sign (=). And then insert the formula with proper parenthesis. If you want to use the cell value as an absolute cell reference, input a dollar sign ($) that won’t change the cell value. At last, press Enter.

Excel Sum Values Based on Date

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 order date. We can utilize the SUMIF and SUMIFS functions for determining our outputs. Let’s get started.

Data set to Sum Values Based on Dates

1. Sum Values Based on A Date Range

If you want to calculate the sum values based on a date range i.e. between two dates, you may use the earlier SUMIF function and comparatively later SUMIFS functions.

Firstly, we’ll see the use of the SUMIF function. For this, 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 is the range of order date, H5 is the start date, E5 is the end date and E5:E14 is the range of bill amount.

Sum Values Using SUMIF function Based on A Date Range

As the SUMIF meets one criteria, we had to use the minus operator as shown in the previous screenshot.

Let’s use the SUMIFS where we don’t need such complexity. Regarding this, just input the following formula

=SUMIFS(E5:E14,D5:D14,">="&H5,D5:D14,"<="&I5)

In this formula, E5:E14 is the range of bill amount, H5 is the start date, D5:D14 is the range of order date and I5 is the end date.

Sum Values Using SUMIFS function Based on A Date Range

2. Sum Values Based on Total Dates

In the previous example, we calculated the sum values based on a date range. Now we’ll calculate the sum values based on given total dates.

In our data set, the order date starts on 1-Jun-21 and ends on 25-Jul-21. We can determine the sum of all bill amounts on this given date by using the following formula.

=SUMIFS(E5:E14,D5:D14,">="&H5,D5:D14,"<="&I5)

In this formula, E5:E14 is the range of bill amount, H5 is the start date, D5:D14 is the range of order date and I5 is the end date.

Sum Values Using SUMIFS function Based on Total Dates

3. Sum Values If Dates are Equal or Same

Let’s imagine some dates are equal though item, bill amount, and others 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 is the range of bill amount, D5:D14 is the range of order date, and 2021,6,7 (year, month, day) means the equal date i.e. 7-Jun-21.

Sum Values if Dates are Equal

4. Sum Values Based on Today’s Date

Assuming the situation where you have to calculate the sum values based on today’s date e.g. 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.

For finding the sum of all bill amounts, just enter the following formula

=SUMIFS($E$5:$E$14,$D$5:$D$14,">"&TODAY(),$D$5:$D$14,">="&TODAY()-$H$6)

Here, E5:E14 is the range of bill amount, D5:D14 is the range of order date, TODAY is a simple function to find the current date and H6 is the number of earlier days.

Sum Values Based on Today's Date

5. Sum Values Based on Dates with A Condition (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 is the range of bill amount, D5:D14 is the range of order date, H5 is the start date, I5 is the end date, C5:C14 is the range of item name, H6 is the name of a specific item.

Sum Values Based on Dates with Condition

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 the double quotes (e.g. input as “<”)
  • Input the formula with correct logic (e.g. don’t input “>” instead of “>=”)
  • Be careful about the file name, file location, and Excel extension name.

Conclusion

This is how you can easily calculate the sum values based on dates. Choose one according to your requirement. And, if you have any suggestions or confusion, please leave them below in the comments section.

Thanks for visiting ExcelDemy, a valuable source of Excel problem-solutions.


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