The SUMPRODUCT function is not limited to product summing, but can also be used for conditional summing, weighted average, etc. 1. The basic usage is to multiply the corresponding elements of multiple arrays and sum, such as calculating the total sales volume and unit price; 2. You can use logical judgment to achieve conditional summing, such as filtering the sales of "East" or "Apple" products, and use the TRUE=1 and FALSE=0 characteristics to combine the array operation; 3. You can calculate the weighted average value, such as multiplying the score and weight and dividing it by the weight sum; 4. When using it, you need to pay attention to the consistency of the scope size, avoiding the entire column reference, treating the text as zero, and using double-negative signs to convert the logical value.

The SUMPRODUCT function in Excel is more powerful than most people give it credit for. At its core, it multiples arrays together and returns the sum of those products — but that's just the start. With a bit of creativity, you can use it for conditional counting, weighed averages, and even replace functions like SUMIFS or COUNTIFS with more flexibility.

Basic usage: Multiply and sum

This is the simplest form of SUMPRODUCT. You give it two or more arrays (ranges of cells), and it multiples the corresponding elements and then sums them up.

For example:

 =SUMPRODUCT(A1:A5, B1:B5)

What happens here:

• It multiples A1 × B1, A2 × B2, and so on.
• Then adds all those results together.

Make sure the ranges are the same size — otherwise, you'll get an error.

Use case: Calculating total sales when you have quantities in column A and prices in column B.

Conditional sum with criteria

One of the cooler tricks with SUMPRODUCT is using it to apply conditions before summing. This works because TRUE equals 1 and FALSE equals 0 in Excel.

Say you want to calculate total sales from region "East":

 =SUMPRODUCT((A1:A10="East") * B1:B10)

How this works:

• (A1:A10="East") creates an array of TRUE/FALSE values.
• Multiplying by B1:B10 converts TRUE to 1 and FALSE to 0, effectively zeroing out rows that don't match “East.”
• Then it sums the remaining values.

You can add multiple conditions:

• Region = "East" AND Product = "Apples":

   =SUMPRODUCT((A1:A10="East")*(B1:B10="Apples")*C1:C10)

Weighted average calculation

If you need a weighed average (where some values count more than others), SUMPRODUCT makes it easy.

Example:

• Grades in column A (scores)
• Weights in column B (importance of each score)

Formula:

 =SUMPRODUCT(A1:A5, B1:B5) / SUM(B1:B5)

That gives you the weighted average. Super useful for grading systems or financial calculations where different factors have different weights.

Tips and things to watch for

Here are a few quick tips to avoid common issues:

• Ranges must be the same size – If one range has 10 cells and another has 12, SUMPRODUCT will throw an error.
• Text values are treated as zero – So if your data includes text in number ranges, it won't break the formula but might give unexpected results.
• Don't use full column references – Like A:A – because it can slow down performance. Stick to defined ranges (eg, A1:A100 ).
• Double negatives or -- can help - Sometimes you need to convert logical tests into numbers. Writing --(A1:A10>5) forces TRUE/FALSE into 1/0.

It's not flashy, but once you get comfortable with SUMPRODUCT, you'll find yourself reaching for it often. It's flexible, doesn't require Ctrl Shift Enter like old-school array formulas, and handles multiple conditions without needing extra helper columns.

Basically that's it.

The above is the detailed content of How to use the SUMPRODUCT function in Excel. For more information, please follow other related articles on the PHP Chinese website!