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.

**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:

`=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**). 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:

`=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**.

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.

`=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**

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.

`=TODAY()`

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:

`=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**H5**= 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 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**

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.