函數SUMPRODUCT是一個集計數與求和于一身的函數，數組公式中函數SUMPRODUCT能取代函數SUM且不用按<Ctrl Shift Enter>三鍵結束。

函數定義：

在給定的幾組數組中，将數組間對應的元素相乘，并返回乘積之和。

語法格式：

SUMPRODUCT(array1, [array2], [array3], ...)

array1：必需。其相應元素需要進行相乘并求和的第一個數組參數。

array2, array3,...：可選。2 到 255 個數組參數，其相應元素需要進行相乘并求和。

1、忽略文本求和

輸入公式：

=SUMPRODUCT(A2:A10)

函數SUMPRODUCT将非數值型的數組元素作為0處理。

公式就是SUMPRODUCT({1;0;2;0;3;0;0;0;5})，即返回11

2、文本型數值求和

輸入公式：

=SUMPRODUCT(B2:B9*1)

B2:B9單元格數值是文本型，*1是将文本型數值轉換成數值型

可以用“--”、“ 0”、“-0”、 “/1”、“^1”等等來轉換

3、單條件計數

求業績大于等于5000的個數：

輸入公式：

=SUMPRODUCT(N(B2:B9>=5000))

B2:B9>=5000部分返回{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}

條件成立返回邏輯值TRUE，條件不成立返回邏輯值FALSE

函數N：将不是數值形式的值轉換為數值形式。日期轉換為序列值，TRUE轉換成1，其他值轉換成0。

同樣的，也可以用“--”、“ 0”、“-0”、 “/1”、“^1”、“*1”等等來轉換

N(B2:B9>=5000)部分返回{0;1;1;0;0;1;1;0}

用函數SUMPRODUCT求和返回4

4、單條件求和

求業績大于等于5000的和：

輸入公式：

=SUMPRODUCT((B2:B9>=5000)*B2:B9)

(B2:B9>=5000)部分返回{FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}

發生四則運算時，TRUE相當于1，FALSE相當于0

(B2:B9>=5000)*B2:B9部分返回{0;7800;6521;0;0;16660;8054;0}

用函數SUMPRODUCT求和返回39035

5、多條件計數

求銷售一部業績大于等于5000的個數：

輸入公式：

=SUMPRODUCT((B2:B9="銷售一部")*(C2:C9>=5000))

多條件計數語法：

SUMPRODUCT((條件區域1=條件1)* (條件區域2=條件2)* (條件區域N=條件N))

6、多條件求和

求銷售一部業績大于等于5000的和：

輸入公式：

=SUMPRODUCT((B2:B9="銷售一部")*(C2:C9>=5000)*C2:C9)

多條件求和語法：

SUMPRODUCT((條件區域1=條件1)* (條件區域2=條件2)* (條件區域N=條件N)*求和區域)

或者：

SUMPRODUCT((條件區域1=條件1)* (條件區域2=條件2)* (條件區域N=條件N),求和區域)

該題也可以用公式：

=SUMPRODUCT((B2:B9="銷售一部")*(C2:C9>=5000),C2:C9)

多條件求和語法中求和條件前使用“*”與“,”的區别：

當求和區域中沒有文本時，“*”與“,”可以互相通用；

當求和區域中有文本時使用“,”，若使用“*”會返回錯誤值#VALUE!

7、單條件計算不重複個數

求業務員不重複的個數：

輸入公式：

=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))

COUNTIF(A2:A9,A2:A9)部分統計區域A2:A9中每個單元格内容在該區域中出現的次數，返回{2;1;2;3;1;1;3;3}

1/{2;1;2;3;1;1;3;3}是将重複數據算作1，比如出現2次的是1/2，出現3次的是1/3，

1/COUNTIF(A2:A9,A2:A9)部分返回

{0.5;1;0.5;0.333333333333333;1;1;0.333333333333333;0.333333333333333}

用函數SUMPRODUCT求和返回5

8、區間求和

求業績大于等于5000、小于15000的和：

輸入公式：

=SUMPRODUCT((B2:B9>=5000)*(B2:B9<15000)*B2:B9)

或者：

=SUMPRODUCT((B2:B9>=5000)*(B2:B9<15000),B2:B9)

9、隔列求和

求入庫、出庫合計：

輸入公式：

=SUMPRODUCT(($B$2:$G$2=H$2)*$B3:$G3)，右拉下拉填充。

10、數組相乘

求總提成：

輸入公式：

=SUMPRODUCT(B2:B9*C2:C9)

B2:B9*C2:C9返回每個業績*對應提成比例，即返回{45.06;390;326.05;136.89;2001.5;1466;402.7;138.75}

然後用函數SUMPRODUCT求和

11、有條件的數組相乘

求“銷售一部”的總提成：

輸入公式：

=SUMPRODUCT((A2:A9="銷售一部")*C2:C9*D2:D9)

(A2:A9="銷售一部")條件成立返回TRUE，條件不成立返回FALSE，即{TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

然後*C2:C9返回的是銷售一部的業績，即{1502;7800;0;0;20015;14660;0;4625}

再*D2:D9返回的就是銷售一部對應的提成，即{45.06;390;0;0;2001.5;1466;0;138.75}

最後用函數SUMPRODUCT求和

12、帶"~"求和

求業務員中有帶“~”的業績和：

輸入公式：

=SUMPRODUCT(ISNUMBER(FIND("~",B2:B9))*C2:C9)

函數SUMPRODUCT不能使用通配符，但可以與函數FIND相結合完成對應功能

FIND("~",B2:B9)部分找到“~”在各業務員字符串中的位置，沒有“~”返回錯誤值#VALUE!，該部分返回{2;#VALUE!;3;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!}

函數ISNUMBER：判斷一個值是否為數值，返回TRUE或FALSE。

ISNUMBER(FIND("~",B2:B9))部分返回{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE}

再*C2:C9返回帶“~”的各業務員的業績{1502;0;6521;0;0;14660;0;0}

最後用函數SUMPRODUCT求和。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!