Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Excel SUMPRODUCT Function Based on Date Range

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.

sumproduct date range

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.

Apply SUMPRODUCT Function on Date Range (Between Two Dates)

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.

Insert Excel SUMPRODUCT to Find Sales after Fixed Date

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.

Use SUMPRODUCT to Determine Sales Before 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.

Implement Excel SUMPRODUCT Function to Find Sales in a Month

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.

Apply SUMPRODUCT If Date Range Includes 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.


Further Reading

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo