SUMPRODUCT函數(shù)不僅限于乘積求和，還可用于條件求和、加權平均等。1. 基本用法是將多個數(shù)組對應元素相乘后求和，如計算銷量與單價的總銷售額；2. 可通過邏輯判斷實現(xiàn)條件求和，如篩選“東部”或“蘋果”產(chǎn)品銷售額，利用TRUE=1、FALSE=0特性結合數(shù)組運算；3. 可計算加權平均值，如成績與權重分別相乘后除以權重和；4. 使用時需注意范圍大小一致、避免整列引用、文本視作零及使用雙負號轉換邏輯值。

The SUMPRODUCT function in Excel is more powerful than most people give it credit for. At its core, it multiplies 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, weighted 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 multiplies the corresponding elements and then sums them up.

For example:

=SUMPRODUCT(A1:A5, B1:B5)

What happens here:

• It multiplies 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 weighted 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 (e.g., 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.

基本上就這些。

以上是如何在Excel中使用Sumproduct函數(shù)的詳細內容。更多信息請關注PHP中文網(wǎng)其他相關文章！