When working with data sets that contain dates, you may need to sum the values based on the provided date regularly. 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 requirements.
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.
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.
Right now, we have to calculate the number of sales using the SUMPRODUCT function from different perspectives. So, let’s get started.
Read More: How to Use SUMPRODUCT with Criteria in Excel
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.
2. Insert Excel SUMPRODUCT to Find Sales after the 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.
Read More: How to Use SUMPRODUCT Function with Multiple Columns in Excel
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 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.
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 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.
Read More: How to Use SUMPRODUCT to Lookup Multiple Criteria in Excel
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: Excel SUMPRODUCT with Multiple Criteria in Same Column
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.
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.
Download Practice Workbook
You can download the practice workbook from here.
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.
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
Get FREE Advanced Excel Exercises with Solutions!
