Image by Editor

Most people think SUMPRODUCT is just for multiplying arrays and then adding them up. But it’s one of the most powerful and flexible functions in Excel, often beating SUMIFS, COUNTIFS, and even AVERAGEIFS in tricky situations. SUMPRODUCT can act as a conditional engine, a dynamic counter, and even a statistics tool, all in a single function that evaluates arrays logically and numerically. In this tutorial, we will show 3 surprising things you can do with the SUMPRODUCT function. 

1. Multi-Criteria Sums More Flexible Than SUMIFS

SUMIFS usually handles conditional summation, but it breaks down when the criteria become irregular. SUMIFS is great, but it’s limited to “AND” logic across criteria and doesn’t handle “OR” and “NOT” as naturally, inequality across ranges, or criteria based on array expressions. SUMPRODUCT, on the other hand, treats TRUE/FALSE expressions as 1s and 0s, allowing you to build almost any logical condition.

Imagine a sales dataset with sales information across different regions. 

Multiple Criteria with OR Logic:

Let’s calculate total units sold in the North OR East regions across all regions. SUMIFS would require restructuring, but SUMPRODUCT solves it with one expression:

=SUMPRODUCT( ((B2:B45="North") + (B2:B45="East")) * C2:C45 )

The formula combines the two logical tests, converting each match into a 1. When the function multiplies this combined array by the Units Sold amounts, it returns the total Units Sold for both regions in a single step. SUMPRODUCT’s ability to combine logical expressions gives it a level of control that SUMIFS cannot achieve without restructuring the dataset.

Multiple Criteria with AND + OR Logic:

Suppose you want to sum Laptop sales from either the North OR the East region. SUMPRODUCT handles it naturally.

=SUMPRODUCT(((B2:B45="North")+(B2:B45="East"))*(A2:A45="Laptop")*E2:E45)

This formula works by combining OR and AND logic inside SUMPRODUCT. It checks whether each row’s region is North or East, and because the two conditions are added, it creates an OR test. The product condition is then multiplied by it, which applies an AND test so only Laptop rows remain. SUMPRODUCT multiplies these logical results by Revenue, so it adds up values from the Revenue column only when the product is a Laptop and the region is either North or East.

Complex Conditional Logic Made Easy with SUMPRODUCT:

You can even build complex nested conditions that would require multiple helper columns or array formulas otherwise:

=SUMPRODUCT(((B2:B45="East")+(F2:F45="Q1"))*(E2:E45>2000)*E2:E45)

This sums sales that exceed $2,000 where the region is East OR the quarter is Q1. Try building that with SUMIFS and you’ll quickly appreciate SUMPRODUCT’s elegance.

2. Flexible Multi-Criteria Counts Without COUNTIFS Limitations

COUNTIFS works well for simple conditions, but fails to count based on complex logic such as “values between two different ranges,” “dates matching a condition derived from another column,” or “criteria that depend on text length or formulas.” SUMPRODUCT makes conditional counting behave like conditional filtering.

Suppose you want to count how many Laptops sold more than 20 units, a useful check for identifying high-performing items. The formula becomes:

=SUMPRODUCT((A2:A45="Laptop") * (C2:C45>20))

The multiplication creates an AND condition; both expressions must be 1 for a row to be counted. This kind of compound logic becomes essential when you need functional rules, variable thresholds, or multi-column dependencies that simple COUNTIFS cannot process.

Using Functions (e.g., LEFT):

Suppose you want to count customers whose orders exceed $500 and whose names start with “A”. COUNTIFS does not support functions like LEFT directly inside its criteria, but SUMPRODUCT evaluates them naturally:

=SUMPRODUCT((LEFT(H2:H45,1)="A") * (E2:E45>500))

The multiplication works as an AND operator: rows must satisfy both expressions to contribute a 1 to the total. Because SUMPRODUCT reads the array results directly, it accepts functional criteria, text manipulation, and mathematical comparisons that COUNTIFS would reject. As data complexity increases, this flexibility becomes essential.

3. Weighted Averages and Custom Summary Statistics

Weighted averages are crucial for accurate analysis when values have different levels of importance, but Excel doesn’t have a built-in WEIGHTED.AVERAGE function. Whenever you have values and corresponding weights, such as grades and credit hours, item scores and importance weights, or prices and quantities, you need to use the SUMPRODUCT function.

SUMPRODUCT multiplies corresponding array elements and returns the sum; it is ideal for any weighted calculation. It can perform both multiplication and aggregation simultaneously.

=SUMPRODUCT( Values, Weights ) / SUM(Weights)

Weighted Average Price:

Suppose you want the weighted average price, weighted by units sold. You can simply multiply units by price and divide by total units.

=SUMPRODUCT(C2:C45, D2:D45) / SUM(C2:C45)

This formula returns a weighted average price based on how many units of each product were sold. The same pattern works for weighted performance scores, credit-hour GPA calculations, weighted defect rates, or any situation where one variable needs to influence another.

Conditional Weighted Average Price for a Specific Product:

Often, you do not want a global average; you want it for a particular product or subgroup. For example, consider the weighted average price of Laptop orders only.

=SUMPRODUCT((A2:A45="Laptop")*E2:E45) / SUMPRODUCT((A2:A45="Laptop")*C2:C45)

The logical test produces 1 for Laptop rows and 0 for all others. This lets you compute a clean weighted average for any subset: a single product, a specific region, or a combination of conditions.

Share of Total Revenue as a Percentage:

Another useful summary statistic is the share of total revenue contributed by a particular group. For instance, you might want the percentage of revenue coming from the North region.

=SUMPRODUCT((B2:B45="North")*E2:E45) / SUMPRODUCT(C2:C45, D2:D45)

Format the result as a percentage, which shows the fraction of overall revenue generated by North. You can adapt the same pattern to calculate the revenue share of a specific product, a region–product combination, or any other filtered group by adjusting the logical test inside SUMPRODUCT.

Average Units for a Filtered Group:

SUMPRODUCT can also be used to compute averages over a filtered subset of rows. Let’s calculate the average number of units per order for all products except the Laptop.

=SUMPRODUCT((A2:A45<>"Laptop")*C2:C45) / SUMPRODUCT(--(A2:A45<>"Laptop"))

The double minus (–) converts TRUE/FALSE values to 1 and 0 so that SUMPRODUCT can treat them as numbers to add. This pattern generalizes nicely. Any time you want “average of X for rows that meet some condition,” think in terms of SUMPRODUCT (condition * X) / SUMPRODUCT(condition). The first part gives the sum for just the relevant rows; the second part gives the number of relevant rows.

Wrapping Up

This tutorial shows 3 surprising things you can do with the SUMPRODUCT function. SUMPRODUCT’s real power comes from its ability to handle multiple conditions with Boolean arithmetic rather than nested functions. It can perform calculations on filtered data without actually filtering and combine logical and mathematical operations in a single formula. While SUMPRODUCT is powerful, it can be slower than SUMIFS for very large datasets with simple criteria because it evaluates every row. Use SUMIFS for straightforward cases where you just need simple AND logic with exact matches. But reach for SUMPRODUCT when you need OR logic, complex nested conditions, weighted calculations, or any of the advanced statistical operations we’ve covered.