Description
Use this function to multiply ranges together and return the sum of the products. Supported in Chains.
語法
SUMPRODUCT(range_1, […, range_254])
Inputs
This function accepts the following arguments:
| 名稱 | 必要 | 說明 | Valid input |
|---|---|---|---|
range_1 |
是 | The first range of cells to be multiplied and then totaled. If no other range is provided, then the values are multiplied by 1. | A cell, a cell range, or a formula which results in a cell range. |
range_n |
沒有 | The subsequent range(s) of cells to be multiplied and then totaled. Up to 254 additional ranges can be included. | A cell reference (including a range), a number, or a formula which results in either of these. |
範例
範例資料
| A | B | C | 暗示性的對話 (D) | E | F |
|---|---|---|---|---|---|
| 日期 | 色彩 | 狀態 | 數量 | Cost | 總計 |
| 2-Jan | Red | TX | 4 | $23.75 | $95.00 |
| 18-Feb | Blue | OH | 3 | $27.80 | $83.40 |
| 15-Mar | 綠色 | TX | 3 | $45.23 | $135.69 |
| 13-Apr | Red | CO | 4 | $58.60 | $234.40 |
| 30-Apr | Red | IL | 2 | $55.10 | $110.20 |
| 20-May | Red | AZ | 1 | $48.90 | $48.90 |
| 14-Jun | 綠色 | WA | 2 | $52.30 | $104.60 |
| 22-Jul | Red | NY | 4 | $62.75 | $251.00 |
| 11-Aug | 綠色 | TX | 4 | $41.65 | $166.60 |
| 19-Aug | 綠色 | OR | 1 | $43.70 | $43.70 |
| 3-Sep | 綠色 | FL | 1 | $33.50 | $33.50 |
| 6-Oct | 綠色 | MA | 3 | $39.15 | $117.45 |
| 25-Oct | Blue | GA | 1 | $29.99 | $29.99 |
| 7-Nov | Blue | WA | 2 | $18.99 | $37.98 |
| 9-Dec | Red | TX | 3 | $37.42 | $112.26 |
Sample formulas
| 公式 | 說明 | 結果 |
|---|---|---|
=SUMPRODUCT(D2:D16, E2:E16) |
Each cell in column D (Quantity) is multiplied by its corresponding cell in the same row in column E (Cost), and the results are totaled. | $1604.67 |
=SUMPRODUCT(D2:D16) |
Each cell in column D (Quantity) is multiplied by 1, and the results are totaled. | 38 |
附註
- SUMPRODUCT supports up to 255 ranges.
- SUMPRODUCT treats non-numeric items (including text, errors, and logical values) as zeros.
- All ranges must be the same size or SUMPRODUCT will return a #VALUE! error.
提示
- For best performance, SUMPRODUCT should not be used with full column (A:A) references.
Related functions
ABS
EXP
LN
MOD
N
POWER
PRODUCT
SIGN
SQRT
SUBTOTAL
SUM
SUMIF
SUMIFS
SUMPRODUCT