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

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

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.

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

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

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