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. SUMPRODUCT, one of the versatile functions of Excel gives us the opportunity in a productive way.

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

**Table of Contents**hide

## Download Practice Workbook

## The Basics

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.

### What is SUMPRODUCT Function

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

The syntax of SUMPRODUCT function is

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

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.

### How to Enter a Formula in Excel

Do you know how can we insert a formula in Excel?

Entering a formula in the Excel formula bar is quite a simple task.

First, you have to select a blank cell where you want to show the output. Then input an **equal sign (=)**. And then insert the formula with proper parenthesis. At last, press **Enter**.

More importantly, if the function is an array formula, you have to press **CTRL+SHIFT+ENTER** to get the output. But if you have **Microsoft 365** (I have used this), you don’t need to do that. I have used array formulas in all methods in this tutorial. So be careful about that.

## SUMPRODUCT Date Range

Let’s have a glimpse at the following dataset. Some** Items** are provided with their** Order Date, Quantity, and Unit Price**. Besides,** Sales** amounts are given which can be calculated easily as it is a multiplication of quantity and unit price of the items.

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

Let’s get started.

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

If you need to calculate the total sales between two dates, you will calculate that using the **SUMPRODUCT** function.

In that case, the formula takes the following form.

`=SUMPRODUCT((C5:C16>=I5)*(C5:C16<=J5)*F5:F16)`

Here, **C5:C16** is the cell range for order date, **I5** is the start date,** J5** is the end date,** F5:F16** is the number of sales.

### 2. SUMPRODUCT Greater Than Date

Assuming that date is fixed. Later, you have to calculate the number of sales after the fixed date.

In such a situation, the formula will be like the following

`=SUMPRODUCT((C5:C16>=I4)*F5:F16)`

Here, **C5:C16** is the cell range for order date,** >=** is the logical operator for greater than or equal,** I4** is the fixed date, **F5:F16** is for the number of sales.

### 3. SUMPRODUCT Less than Date

It is similar to the earlier method except you have to put less than logical operator instead of greater than operator.

So, the formula will be

`=SUMPRODUCT((C5:C16<=I4)*F5:F16)`

Here, **C5:C16** is the cell range for order date, **<=** is the logical operator for less than or equal, **I4** is the fixed date, **F5:F16** is for the number of sales.

### 4. SUMPRODUCT in a Month

In this way, you have to take the first date of a month as the start date and the last of the month as the end date. Then follow the formula like the formula for a date range.

`=SUMPRODUCT((C5:C16>=I5)*(C5:C16<=J5)*F5:F16)`

Here, **C5:C16** is the cell range for order date,** I5** is the start date,** J5** is the end date, **F5:F16** is the number of sales.

### 5. SUMPRODUCT If the 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.

Here, 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). The syntax of the function is

`=WEEKDAY (serial_number, [return_type])`

The arguments are the following-

*serial_number *– The date for which you want to get the day of the week.

*return_type* – [optional] A number representing a day of a week mapping scheme.

The Excel **SUMPRODUCT** function will then be used to summarize all the numbers corresponding to the designated weekend days. The formula will be

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

Here, **C5:C16** is for the order date, **2** is for Monday (generally return_type of **1** refers to Sunday), **6** is for Saturday, and **E5:E16** is for the number of sales.

The formula sums together all the sums pertaining to Saturday and Sunday.

### 6. SUMPRODUCT 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 Monitor within a specific period, you may proceed with the following formula.

`=SUMPRODUCT((B5:B16=I6)*(C5:C16>=I5)*(C5:C16<=J5)*F5:F16)`

Here, **B5:B16** is the range for items, **I6** is for an item namely Monitor, **C5:C16** is for order date, **I5** is the start date, **J5** is the end date, **F5:F16** is the number of sales.

### 7. SUMPRODUCT 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 the products that meet the date range.

The formula will be

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

**C5:C16** is the cell range for order date, **H5** is the start date,** I5** is the end date,** D5:D16** is the quantity, and** E5:E16** is the unit price.

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