SUMPRODUCT Date Range [7 Productive Methods]

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.

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.

Dataset for SUMPRODUCT Date Range

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.

SUMPRODUCT on a Date Range (Between Two Dates)

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.

SUMPRODUCT Greater Than Date

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.

SUMPRODUCT Less than Date

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.

SUMPRODUCT in a Month

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.

SUMPRODUCT If the Date Range Includes Weekends

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.

SUMPRODUCT Date Range with Single Criteria

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.

SUMPRODUCT Date Range with Multiple Criteria

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo