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.

**Table of Contents**Expand

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

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.

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

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

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

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

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.