Sum Values Based on Date in Excel (4 Ways)

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.

Dataset to Calculate Excel Sum Values Based on Date


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:



  • 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). 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:


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.

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.



  • 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

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.


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:



  • E5:E14 = the range of bill amount
  • D5:D14 = the range of order date
  • H4 = the current date
  • H5 = 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 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!
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

  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