Excel SUMPRODUCT Function Based on Date Range (7 Examples)

An Introduction to the SUMPRODUCT Function

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

• Syntax:

SUMPRODUCT is an extraordinarily multifaceted but rather flexible function suitable for summing, similar to SUMIFS.

The syntax of the SUMPRODUCT function is:

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

• Arguments:

The following arguments are in the function:

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

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

Excel SUMPRODUCT Function Based on Date Range: 7 Productive Examples

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.

The task is to calculate the number of sales using the SUMPRODUCT function from different perspectives.

Read More: How to Use SUMPRODUCT with Criteria in Excel

Method 1 – Applying SUMPRODUCT Function on Date Range to Calculate the Total Sales Between Two Dates

Steps:

• Select Cell G6 and enter the formula below:

• Press Enter to see the result.

Method 2 – Inserting Excel SUMPRODUCT to Find Sales After the Fixed Date

For the second example, you need to assume that the date is fixed. Then, you have to calculate the number of sales after the fixed date. Let’s say the fixed date in question is July 1, 2021.

Steps:

• Select Cell G7.
• Copy the following formula:

• Hit Enter to see the result.

Read More: How to Use SUMPRODUCT Function with Multiple Columns in Excel

Method 3 – Using SUMPRODUCT to Determine Sales Before the Fixed Date

This example is similar to the earlier method except you have to put a less-than-logical operator instead of a greater-than operator.

Steps:

• Select Cell G7 and enter the formula below:

• Press Enter to see the sales amount before the fixed date.

Read More: How to Use SUMPRODUCT IF in Excel

Method 4 – Implementing Excel SUMPRODUCT Function to Find Sales in a Month

For this purpose, you have to take the first date of a month as the start date and the last day of the month as the end date. In this case, we will find the sales amount for June.

Steps:

• Select Cell G6.
• Copy the formula below to Cell G6:

• Hit Enter to see the sales amount for June.

Method 5 – Applying SUMPRODUCT to a Date Range That Includes Weekends

Suppose 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, you’ll need the WEEKDAY function. The function returns a day of the week from a date, depending on the kind of return (return type). Then, the 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 as follows:

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, the number 1 will represent Monday, and the number 7 will represent Sunday.

Steps:

• Select Cell H7 and copy the formula below:

• Press Enter to see the sales amount on weekends.

Read More: How to Use SUMPRODUCT to Lookup Multiple Criteria in Excel

Method 6 – Using SUMPRODUCT Based on a Date Range With Single Criterion

Let’s say you want to calculate the total sales of Monitors within a specific period.

Steps:

• Select Cell G7 and enter the formula below:

• Hit Enter to see the sales amount with a single criterion.

Read More: Excel SUMPRODUCT with Multiple Criteria in Same Column

Method 7 – Inserting SUMPRODUCT Based on a Date Range With Multiple Criteria

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

You can find the sales by the multiplying the quantity and unit price for each item. But what will happen if a date range is given?

In such a scenario, you can also use the SUMPRODUCT function. It will add up the products that meet the date range.

Steps:

• Select Cell H7.
• Copy the following formula:

• Hit Enter to see the result.

Things to Keep in Mind

• Be careful while inserting the formula, especially with the logical operator and parentheses.
• Be cautious about the file name, file location, and the extension name of Excel.

Download Practice Workbook

You can download the practice workbook from here.

Related Articles

• SUMPRODUCT Across Multiple Sheets in Excel
• SUMPRODUCT for Counting with Multiple Criteria in Excel
• [Solved] SUMPRODUCT with Multiple Criteria Not Working in Excel

<< Go Back to Excel SUMPRODUCT Function | Excel Functions | Learn Excel