Grab our practice workbook 👉 HERE and follow along.
The SUMPRODUCT function in Excel is a powerful tool that doesn’t require the usual Ctrl + Shift + Enter (CSE) combination for array functions.
What does SUMPRODUCT do: It can simplify many tasks, such as summing, counting, and even replacing functions like SUMIFS, INDEX, and MATCH.
Basic Use of SUMPRODUCT
SUMPRODUCT is commonly used to sum the results of multiplications. For example, if you have Price and Quantity data, you can calculate Total Revenue. You can do this without adding the extra helper column E to multiply price by quantity.
With SUMPRODUCT we can multiply the values in columns C (price) and D (quantity) directly like this:
=SUMPRODUCT(C4:C9, D4:D9)
Explanation of the Formula
- Multiplication and Summing: This formula multiplies each cell in the Price column (C) by its corresponding cell in the Quantity column (D) and then sums all the results. The result is the same as manually summing the values from an extra helper column.
- Same Size Ranges: It’s important to ensure that the ranges for the arrays (C4:C9 and D4:D9) are of the same size. If the ranges are not the same size, the formula will return an error.
SUMPRODUCT for Conditional Sum and Count
SUMPRODUCT is not just a Excel formula for multiplication; it can also handle conditions. This makes it incredibly useful for tasks like summing quantities based on specific criteria.
For example, suppose you want to sum the quantity with SUMPRODUCT if the product is ‘Shirt A’.
Here’s how you can do it:
=SUMPRODUCT((B4:B9=H3) * D4:D9)
Explanation of the SUMPRODUCT Formula
- Condition Inside Parentheses:
(B4:B9=H3)checks each cell in the range B4:B9 to see if it equals ‘Shirt A’. This part of the formula creates an array of TRUE and FALSE values. - Multiplication Operator (*): Use the multiplication operator
'*'instead of a comma to combine the condition with the quantity range. This ensures that only the quantities where the condition is TRUE are included in the sum. - Correct Use of Parentheses: You need to put the condition in parentheses. This ensures that Excel processes the condition before multiplying. Without the parentheses, the formula may not work as intended.
Importance of Using the Multiplication Operator (*)
Using * instead of a comma allows for more complex conditions and ensures that only the rows meeting the condition are included in the calculation. This approach is not only more powerful but also more flexible, allowing you to use SUMPRODUCT with multiple criteria if needed.
How Does This Work?
Here’s a detailed look at how this formula operates:
- Checking the Condition: The first argument (B4:B9=H3) checks if each entry in B4:B9 is ‘Shirt A‘. This results in an array of TRUE and FALSE values:
- Example Result: FALSE, FALSE, TRUE, FALSE, TRUE, FALSE
- Multiplication with Boolean Values: When Excel multiplies these Boolean values (TRUE or FALSE) by the quantity range (D4:D9), it converts TRUE to 1 and FALSE to 0. This makes the quantity array:
- Converted Quantities: 0, 0, 1, 0, 1, 0
- Summing the Results: SUMPRODUCT then sums these converted values, effectively summing only the quantities where the product is ‘Shirt A’.
To calculate the revenue for ‘Shirt A’, you can extend the SUMPRODUCT formula like this:
=SUMPRODUCT((B4:B9=H3) * C4:C9 * D4:D9)
This formula multiplies the quantities and prices for ‘Shirt A’ and sums the results, giving you the total revenue for ‘Shirt A’.
SUMPRODUCT With Double Negative (–)
Another powerful technique in Excel involves using the double negative (–) to handle conditional sums with SUMPRODUCT. This method converts Boolean values (TRUE/FALSE) into numerical values (1/0), allowing SUMPRODUCT to process them correctly.
Suppose you want to sum the quantity with SUMPRODUCT if the product is ‘Shirt A’ by using the double negative method.
Here’s how you can do it:
=SUMPRODUCT(--(B4:B9=H3), D4:D9)
Explanation of the SUMPRODUCT Formula
- Double Negative (–): The double negative before (B4:B9=H3) converts the array of TRUE and FALSE values into 1s and 0s. This ensures that the SUMPRODUCT formula can handle the array correctly.
- Condition Inside Parentheses: (B4:B9=H3) checks each cell in the range B4:B9 to see if it equals ‘Shirt A’. This part of the formula creates an array of TRUE and FALSE values.
- Use of Comma: The comma is used to separate the condition from the quantity range. After the double negative conversion, SUMPRODUCT can process the array without issues.
To calculate the revenue for ‘Shirt A’ using the double negative method, extend the SUMPRODUCT formula like this:
=SUMPRODUCT(--(B4:B9=H3), C4:C9, D4:D9)
Why Use Double Negative vs. Multiplication Operator (*)
Both the double negative and the multiplication operator achieve the same result in converting TRUE/FALSE to 1/0 for the purposes of calculations in SUMPRODUCT. Here are some advantages of using the double negative method:
- Readability: The double negative (–) makes the intention of converting Boolean values (TRUE/FALSE) to numerical values (1/0) explicit. This can enhance readability, especially for users familiar with this technique.
- Standard Practice: Both methods are widely used and recognized. However, using double negatives is a common practice in Excel for converting Boolean values, which might be more familiar to some users.
Using SUMPRODUCT for OR and AND Conditions
SUMPRODUCT can handle both OR and AND conditions, unlike SUMIFS or COUNTIFS. This makes it incredibly flexible for data analysis.
Example: Counting Instances with OR and AND Conditions
Let’s say you want to count instances where the company is “Company A” (in cell I3) or “Company C” (in cell K3), the year is 2024 (in cell I4), and the month is January (in cell I5).
Here’s how you do it:
Basic OR Condition:
=SUMPRODUCT((A3:A11="Company A")+(A3:A11="Company C"))- Use the plus sign (+) for OR conditions. Enclose the entire OR argument in brackets.
- This formula counts how many times “Company A” or “Company C” appears. The result is 7
Adding AND Conditions:
=SUMPRODUCT(((A3:A11=I3) + (A3:A11=K3)) * (B3:B11=I4) * (C3:C11=I5))- Use the multiplication sign (*) for AND conditions. Enclose each condition in brackets.
- This counts instances where the company is “Company A” or “Company C,” the year is 2024, and the month is January. The result is 5.
How it Works:
- Step 1: ((A3:A11=I3) + (A3:A11=K3)) creates an array of TRUE/FALSE values for the OR condition.
- Step 2: Multiplication (*) converts these values to 1/0 for the AND conditions.
- Step 3: SUMPRODUCT multiplies these arrays and sums the results.
💡 The AND is symbolized by a * (multiplication) sign, while an OR with a + (plus) sign.
Example: Summing Quantities
To sum quantities for the specified conditions:
=SUMPRODUCT(((A3:A11=I3) + (A3:A11=K3)) * (B3:B11=I4) * (C3:C11=I5) * E3:E11)- Result: This formula sums quantities where the conditions are met. The result is 470
Example: Calculating Revenue
To calculate revenue:
=SUMPRODUCT(((A3:A11=I3) + (A3:A11=K3)) * (B3:B11=I4) * (C3:C11=I5) * E3:E11 * D3:D11)- Result: This formula multiplies quantities by prices for the conditions and sums the results. The result is $98,200.
Important Tips
- Bracket Placement: Always enclose conditions in brackets to avoid errors or wrong results.
- Same Size Ranges: Ensure all array ranges are the same size to prevent errors
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel SUMPRODUCT Function with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
