The SUMPRODUCT function is a built-in excel function that allows users multiply corresponding elements in two or more arrays and then output the sum of the results. As a modeler, I remember using this function to quickly input CAPEX requirement for Generator replacements whereby we had a section for the various Generator capacity count needed and their corresponding prices. Without the SUMPRODUCT function, we would have needed to individually multiply the count and unit price and then add up these amounts in an overarching SUM formula. The resulting formula would have been very long and difficult to follow. Thankfully, the SUMPRODUCT was used to achieve the result with just 2 arguments. Don’t worry, we would illustrate how I was able to use the SUMPRODUCT for this after we take a few examples to introduce you to the function. Let’s start with the syntax and argument.
Syntax & Argument
The syntax for the SUMPRODUCT Function is as follows:
=SUMPRODUCT(array1, [array2], …)
In the above syntax, you can see only 2 arguments, however, please note that the SUMPRODUCT function can take between 1 and 255 arguments which are the arrays or ranges of cells you want to multiply and sum.
- array1: This is the first array or range of cells to multiply and sum.
- array2: The SUMPRODUCT function can take only one argument, so this is optional, and it represents the second array or range of cells to be multiplied and summed.
For a SUMPRODUCT with only one argument (array1), you would have the result as the sum of values in the range of cells as there is no corresponding range of cells to multiply array1 with. Let’s look at some of the other usage notes of the function below:
Usage Notes
- The SUMPRODUCT can perform array calculations without the need Ctrl + Shift + Enter which is usually required for other array formulas in excel.
- It is the most versatile function in excel to compare data in 2 or more arrays and calculate data with multiple criteria.
- The functionality of the SUMPRODUCT function can be extended by combining with other functions such as COUNTIF and SUMIF to perform more complex calculations.
SUMPRODUCT Examples
For this section, we have a data set containing information on sales by a Jewelry company across all its retail stores in 4 states namely Colorado (CO), Florida (FL), California (CA) and Texas (TX).

We would start off with a basic example, and then progress to more complex examples. We have 4 criteria to apply the SUMPRODUCT function to as seen below:

Basic Example
The first example is a basic example, we need to return the sum total of all precious stones sold. This is likened to adding up all $ values in our helper column “F”. We can however skip having to prepare the helper column “F” by using the SUMPRODUCT function, we can do this by using the formula below:

We can see from the above screenshot that the result using our SUMPRODUCT Function is equal to when we individually calculate the dollar value of each precious stone sold and add it up. Now let’s move on to more complex examples which would center on returning the SUMPRODUCT based on certain criteria / conditions.
SUMPRODUCT With Conditions
Single Condition
Building on from the previous section with our basic example, we would be illustrating how to use the SUMPRODUCT Function with conditions. Our first example is returning the total sales value of the Diamond precious stone. To do this, we use the formula below:

Double negative (–)
Notice that we have a double negative (–) just after the first bracket of our SUMPRODUCT. The double negative is used in advanced excel to force TRUE and FALSE values in excel to 1s and 0s. This is because having excel return a TRUE or FALSE Value in a SUMPRODUCT function to multiply another column would result in a zero value.
In effect, the double negative forces excel to check each value in the array for the condition (C3:C13) and assign a number 1 to each value that meets the condition (TRUE) and a zero (0) to values that don’t meet the condition. Subsequently, the result of this check in the first array is used to multiply the numerical values in the second array (F3:F13) to return the proper total value.

Effectively, our SUMPRODUCT function is evaluated as follows:
The SUMPRODUCT then multiplies array 1 and array 2 together resulting in a single array:
The final step would be for the SUMPRODUCT function to add up all numbers from the previous step to arrive at 95,000
Multiple Conditions
We have shown how to use the SUMPRODUCT function with a single condition, let’s now delve into utilizing it for multiple condition testing before adding up. Using the previous example, we can see that we need to return the total sales of Blue Sapphire in California. What better way to achieve this than the SUMPRODUCT function. We would use the formula below to return the value needed:

Notice that the only difference between the single and multiple condition syntax is that we included a second condition by multiplying the first criteria with our next criteria range and criteria “*(C3:C13=”Blue Sapphire”)“. You can use the same logic to include more conditions.
The last example for this section is to return the total amount for Diamond sales in Colorado, for that, we use the formula below:

Practical use of SUMPRODUCT in Financial Modeling
As promised, we would provide an example of how we utilized the SUMPRODUCT function to calculate and return our Generator CAPEX requirements. In order not to bore you with the details, we are limiting it to Q1 (January to March) CAPEX submission only.

As you can see from the above, we have a QTY column and Price per unit column, and these two columns should be multiplied to get the total for each CAPEX requirement. We however want to skip that and use the SUMPRODUCT Function to return monthly CAPEX requirements for the Generator Category. This means that we need to apply the function to filter for 2 conditions namely the category and month placed.
We would create a section on the worksheet for our calculations

Now, on to the formula to use. This would be similar to the syntax used under the multiple condition section, however instead of manually hardcoding the conditions, we would be selecting the condition / criteria from cells. Remember that our two conditions are the Category (cell I7) and the date (ranging from cells J6:L6). The first formula to return CAPEX requirement for Generators for January 2023 is as follows:

Copy and paste the formula to the other cells (Cells K7 and L7) and you are done. Remember, Ctrl + C to copy and Ctrl + V to paste.

Let’s isolate Generators and calculate with a helper column to see if it adds up.

Both the formula calculation and calculation through a helper column add up to $180,011.