How to Use SUMPRODUCT with Month and Division Criteria in Excel

In this article, we will learn how to use the Excel function SUMPRODUCT to calculate the sum of products of two or more arrays based on a specific month and a division operation. We will also explain the basic theory behind the function, the procedures to apply it, and some alternative approaches. We will use a sample data set of sales orders to illustrate the examples.

Basic Theory

The SUMPRODUCT function is a versatile and powerful function that can perform multiple calculations and return a single result. The syntax of the function is:

=SUMPRODUCT (array1, [array2],…)

The function takes one or more arrays or ranges as arguments and multiplies the corresponding elements in each array. Then, it adds up the results of the multiplication and returns the sum.

For example, if we have two arrays A and B, the SUMPRODUCT function will calculate the sum of products as follows:

=SUMPRODUCT (A, B) = A1B1 + A2B2 + A3*B3 + …

We can also use criteria or conditions in the SUMPRODUCT function to filter the arrays based on certain values. For example, we can use logical operators such as =, <, >, <=, >=, <>, and & to compare the elements in the arrays with a given value or expression. We can also use functions such as MONTH, YEAR, DATE, etc. to extract the date components from the arrays and use them as criteria.

For example, if we want to calculate the sum of products of two arrays A and B only for the month of January, we can use the MONTH function and the equal operator as follows:

=SUMPRODUCT ( (MONTH (A) = 1) * B)

The MONTH function will return the month number of each element in array A, and the equal operator will compare it with 1, which represents January. The result will be an array of TRUE or FALSE values, depending on whether the condition is met or not. Then, we multiply this array by array B, which will convert the TRUE values to 1 and the FALSE values to 0. Finally, we sum up the products of the resulting array and get the answer.

Procedures

To use the SUMPRODUCT function based on a specific month and a division operation, we need to follow these steps:

1. Identify the arrays or ranges that we want to use in the function. For example, we may have a range of dates, a range of sales amounts, and a range of years.
2. Specify the month that we want to use as the criterion. For example, we may want to use November, which is represented by 11.
3. Use the SUMPRODUCT function and enter the first array or range as the first argument. For example, we may enter the range of sales amounts as the first argument.
4. Enter a comma and then enter the second array or range as the second argument. However, before entering the range, we need to divide it by another range or value. For example, we may enter the range of years divided by the range of sales amounts as the second argument.
5. Enter another comma and then enter the third array or range as the third argument. However, before entering the range, we need to use the MONTH function and the equal operator to compare it with the month number that we specified in step 2. For example, we may enter the MONTH function applied to the range of dates and compare it with 11 as the third argument.
6. Close the parentheses and press Enter to get the result.

The final formula will look something like this:

=SUMPRODUCT (sales_amount, years/sales_amount, (MONTH (dates) = 11))

This formula will calculate the sum of products of the sales amount, the years divided by the sales amount, and the month number equal to 11. It will only include the rows that have the date in November.

Example

Let’s use a sample data set of sales orders to demonstrate the SUMPRODUCT function based on a specific month and a division operation. The data set contains the following columns:

• Order ID: A unique identifier for each order
• Order Date: The date when the order was placed
• Sales Amount: The total amount of the order
• Years: The number of years that the customer has been with the company

The data set looks like this:

Suppose we want to calculate the sum of products of the sales amount, the years divided by the sales amount, and the month number equal to 11. We can use the following formula:

=SUMPRODUCT (C2:C6, D2:D6/C2:C6, (MONTH (B2:B6) = 11))

The formula will return $10,000, which is the sum of the following products:

Alternative Approaches

There are some alternative approaches to use the SUMPRODUCT function based on a specific month and a division operation. Here are some of them:

• We can use the SUMIFS function instead of the SUMPRODUCT function. The SUMIFS function can sum the values in a range that meet multiple criteria. The syntax of the function is:

=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

The function takes the following arguments:

• sum_range: The range of cells to sum.
• criteria_range1: The first range of cells to evaluate.
• criteria1: The first criterion to apply.
• criteria_range2, criteria2, …: [optional] Additional ranges and criteria to apply.

For example, we can use the following formula to get the same result as the SUMPRODUCT formula:

=SUMIFS (C2:C6, B2:B6, “>=”&DATE (2023, 11, 1), B2:B6, “<=”&DATE (2023, 11, 30)) / SUMIFS (D2:D6, B2:B6, “>=”&DATE (2023, 11, 1), B2:B6, “<=”&DATE (2023, 11, 30))

This formula will sum the sales amount and the years for the rows that have the date in November 2023, and then divide the two sums to get the result.

• We can also use the SUM and IF functions together instead of the SUMPRODUCT function. The SUM function can add up the values in a range or an array, and the IF function can return a value based on a condition. The syntax of the functions are:

=SUM (number1, [number2],…) =IF (logical_test, value_if_true, [value_if_false])

The functions take the following arguments:

• number1, number2, …: The numbers to sum.
• logical_test: The condition to evaluate.
• value_if_true: The value to return if the condition is TRUE.
• value_if_false: [optional] The value to return if the condition is FALSE.

For example, we can use the following formula to get the same result as the SUMPRODUCT formula:

=SUM (IF (MONTH (B2:B6) = 11, C2:C6 * D2:D6 / C2:C6, 0))

This formula will create an array of products of the sales amount, the years divided by the sales amount, and the month number equal to 11, and then sum up the array to get the result. However, this formula is an array formula, which means that we need to press Ctrl + Shift + Enter to enter it in Excel.