When working with data sets that contain dates, it is possible that you may need to sum the values based on the provided date regularly. The **SUMPRODUCT **is one of the versatile functions of Excel and it gives us the opportunity in a productive way. Today, we will learn to use the **SUMPRODUCT **function based on the date range in Excel.

In this article, I’ll focus on the productive examples to calculate the sum value using the **SUMPRODUCT **function based on a date range and also criteria using the function with proper explanation. Thus, you can adjust the formula according to your requirement.

## Download Practice Workbook

You can download the practice workbook from here.

## Introduction to SUMPRODUCT Function

This section is mainly for beginners. Here, we’ll understand **the SUMPRODUCT function** and the process of entering the formula in Excel. If you think you’ve got these basics, move to the main section.

**Syntax:**

**SUMPRODUCT **is an extraordinarily multifaceted, but rather flexible function that is suitable for summing such as **SUMIFS**.

The syntax of the **SUMPRODUCT **function is:

`=SUMPRODUCT(array1,[array2],...)`

**Arguments:**

There are the following arguments in the function.

** array1** – The first array or range to multiply, then add.

** array2 **– [optional] The second array or range to multiply, then add.

## 7 Productive Examples of Using Excel SUMPRODUCT Function Based on Date Range

Let’s have a glimpse at the following dataset. Some **Items **are provided with their **Order Date** and **Sales **amount. We will use this dataset to explain the examples of the **SUMPRODUCT **function in Excel.

Right now, we have to calculate the number of sales using the **SUMPRODUCT **function from different perspectives.

So, let’s get started.

### 1. Apply SUMPRODUCT Function on Date Range (Between Two Dates)

If you need to calculate the total sales between two dates, you can calculate that using the **SUMPRODUCT **function. Let me show the way to apply the **SUMPRODUCT **function for that purpose.

**STEPS:**

- First of all, select
**Cell G6**and type the formula below:

`=SUMPRODUCT((C5:C16>=G5)*(C5:C16<=H5)*D5:D16)`

- After that, press
**Enter**to see the result.

Here, **range C5:C16** is the cell range for **Order Date**, **Cell G5** is the start date, **Cell H5** is the end date, and **range D5:D16** is the number of sales.

**Read More:** **SUMPRODUCT with Criteria in Excel (5 Methods)**

### 2. Insert Excel SUMPRODUCT to Find Sales after Fixed Date

In the second example, we need to assume that the date is fixed. Later, you have to calculate the number of sales after the fixed date. Suppose, you need to determine the sales after **1st July** **2021**. Let’s observe the steps below to use the **SUMPRODUCT **function to find sales after a fixed date.

**STEPS:**

- Firstly, select
**Cell G7**. - After that, type the formula:

`=SUMPRODUCT((C5:C16>=G4)*D5:D16)`

- Now, hit
**Enter**to see the result.

Here, **range C5:C16** is the cell range for **Order Date**, **>=** is the logical operator for greater than or equal, **Cell G4** is the fixed date, and **range D5:D16** is for the number of sales. So, from **1-Jul-21 **to **25-Jul-21**, the sales amount is $**33,650**.

### 3. Use SUMPRODUCT to Determine Sales Before Fixed Date

In this example, we will determine the sales amount before the fixed date. It is similar to the earlier method except you have to put a less-than-logical operator instead of a greater-than operator.

**STEPS:**

- In the first place, select
**Cell G7**and type the formula below:

`=SUMPRODUCT((C5:C16<=G4)*D5:D16)`

- Now, press
**Enter**to see the sales amount before the fixed date.

Here, **range C5:C16** is the cell range for **Order Date**,** <=** is the logical operator for less than or equal, **Cell G4** is the fixed date, and **range D5:D16** is for the number of sales. So, from **1-Jun-21 **to **8-Jul-21**, the sales amount is $**34,750**.

**Read More: How to use SUMPRODUCT IF in Excel**

### 4. Implement Excel SUMPRODUCT Function to Find Sales in a Month

We can also use the **SUMPRODUCT **function to find sales in a month. For that purpose, you have to take the first date of a month as the start date and the last of the month as the end date. In this case, we will find the sales amount for **June**. So, let’s follow the steps below for more.

**STEPS:**

- In the beginning, select
**Cell G6**. - Now, type the formula below in
**Cell G6:**

`=SUMPRODUCT((C5:C16>=G5)*(C5:C16<=H5)*D5:D16)`

- Finally, hit
**Enter**to see the sales amount for**June**.

Here, **range C5:C16** is the cell range for **Order Date**, **Cell G5** is the start date, **Cell H5** is the end date, and **range D5:D16** is the number of sales.

### 5. Apply SUMPRODUCT If Date Range Includes Weekends

Supposing that the given date range has weekends. And you have to determine the number of sales for the weekends along with the date range.

To do so, we’ll use **the WEEKDAY function**. The function returns a day of the week from a date, depending on the kind of return (return type). Then, the **Excel SUMPRODUCT** function will be used to summarize all the numbers corresponding to the designated weekend days.

**Syntax:**

The syntax of the **WEEKDAY** function is:

`=WEEKDAY(serial_number,[return_type])`

**Arguments:**

The arguments are the following-

** serial_number** – It is the compulsory argument that gets you the desired day of the week.

** return_type** – This is an optional argument. It is a mapping scheme that denotes the serial of the days of the week. For example, if you insert

**1**, then number

**1**will represent

**Monday**and number

**7**will represent

**Sunday**.

**STEPS:**

- In the first place, select
**Cell H7**and type the formula below:

`=SUMPRODUCT((WEEKDAY(C5:C16,2)>=6)*E5:E16)`

- Now, press
**Enter**to see the sales amount on weekends.

Here, **range C5:C16** is for the **Order Date**, **2 **is for **Monday **(generally **return_type** of **1 **refers to **Sunday**), **6 **is for **Saturday**, and **range E5:E16** is for the number of sales. The formula sums together all the sums pertaining to **Saturday **and **Sunday**.

### 6. Use SUMPRODUCT Based on Date Range with Single Criteria

Besides, you can utilize the **SUMPRODUCT **function for single criteria and also based on a date range. For example, if you want to calculate the total sales of **Monitors **within a specific period, you may follow the steps below.

**STEPS:**

- Firstly, select
**Cell G7**and type the formula below:

`=SUMPRODUCT((C5:C16<=G4)*D5:D16)`

- After that, hit
**Enter**to see the sales amount with a single criterion.

Here, **range B5:B16** is the range for **Items**, **Cell G6 **is for an item namely **Monitor**, **range C5:C16** is for the **Order Date**, **Cell G5** is the start date, **Cell H5** is the end date, and **range D5:D6** is the number of sales.

**Read More:** **SUMPRODUCT Function with Multiple Columns in Excel (4 Simple Ways)**

### 7. Insert SUMPRODUCT Based on Date Range with Multiple Criteria

Let’s imagine that the amount of sales is not given. But the quantity and unit price are provided. Now, how can we calculate the sales?

We can find the sales by the multiplication of quantity and unit price for each item. What will happen, if a date range is given?

In such a situation, we also may use the **SUMPRODUCT **function. It will sum up the products that meet the date range.

**STEPS:**

- Firstly, select
**Cell H7**. - After that, type the formula:

`=SUMPRODUCT((C5:C16>=H5)*(C5:C16<=I5)*D5:D16*E5:E16)`

- In the end, hit
**Enter**to see the result.

In this formula, **range C5:C16** is the cell range for **Order Date**, **Cell H5** is the start date, **Cell I5** is the end date, **range D5:D16** is the quantity, and **range E5:E16** is the unit price.

**Read More:** **SUMPRODUCT Multiple Criteria in Excel(3 Approaches)**

## Things to Keep in Mind

- Be careful while inserting the formula, especially for the logical operator and parentheses.
- Also be cautious about the file name, file location, and the extension name of Excel.

## Conclusions

This is how you may easily calculate the sum value using the **SUMPRODUCT** function based on a date range. If you have any suggestions, please leave them below in the comments section.

Thanks for being with me.