Excel的SUM函數9種公式設置範例

1、數組求和：{=SUM((G12:G21>100)*G12:G21)}

[公式說明]：本公式為數組公式，可以對G12:G21區域中大於100的數據進行求和，而排除小於等於100的數據。輸入公式時必須按【Ctrl+Shift+Enter】組合鍵結束，否則無法得到正確結果。

[使用注意]：1、公式中"G12:G21>100"部分表示求和條件，後跟實際求和區域"G12:G21"。如果有多個條件，可一併羅列出來。例如求大於100且小於115的數據之和，公式如下:=SUM(G12:G21>100)*(G12:G21<115)*G12:G21)。2、此數組公式只適用於單個區域求和，如果有多個區域，只能用多個SUM求和，然後相加。例如對G12:G21和H12:H21區域中大於100的數匯總，公式如下:=SUM(SUM((G12:G21>100)*G12:G21),SUM((H12:H21>100)*H12:H21))。3、對於SUM函數的數組公式，可以用SUMPRODUCT函數來代替，從而將數組公式轉換成普通公式。例如本案例的公式用SUMPRODUCT函數後，普通公式如下:=SUMPRODUCT((G12:G21>100)*G12:G21)。

2、數據類型轉換求和：=SUM(VALUE(H5),H6:H10,J5:J10,L5:L10)；=SUM(--(H5),H6:H10,J5:J10,L5:L10)；=SUM((H5)*1,H6:H10,J5:J10,L5:L10)；=SUM((H5)/1,H6:H10,J5:J10,L5:L10)

[公式說明]：SUM函數用於對單元格區域的數據或者邏輯值、表達式進行求和，它有1-255個參數。鑒於本題的特殊性，公式也可以改為"=SUM(區域1)"，函數會忽略區域中的文本。

[使用注意]：1、SUM函數有1-255個參數。參數可以是區域，也可以是表達式。如:=SUM(G5,10*25,G6*8)。2、如果是文本型數字參數，SUM函數是可以直接求和的；如果是引用單元格中的文本型數字(單元格的數字前添加半形單引號「"」),則求和時將忽略。例如G5的值是「"95」，那麼以下公式的結果大不同。=SUM(10,"95")—結果為105,=SUM(10,G5)—結果為10。3、如果一定要對單元格中文本型數字進行求和，可以將它轉換成數值。轉換方法包括多種:利用VALUE函數轉換、利用"--"轉換和"*1"、"/1"方式轉換。例如:=SUM(10,VALUE(G5))、=SUM(10,--(G5))、=SUM(10,(G5)*1)

3、多條件求和1：{=SUM(((G23:G32<100)+(G23:G32>110))*G23:G32)}

[公式說明]：對於兩個範圍求和，即滿足兩個條件中任意一個條件都進行求和，這和同時滿足兩個條件才求和的公式思路不同。"案例2"的使用注意"中已提到，多個條件同時滿足才求和需要用"*"連接所有條件，表示同時滿足多條件，而本案例中對多個條件用"+"來連接，表示對於多條例中滿足任意一個條件就可以進行求和。本公式為數組公式。

[使用注意]：1、多條件滿足任意條件即求和，在設置公式時對於所有條件需要用"+"連接，如本例中"(G23:G32<100)+(G23:G32>110)",然後對條件用括弧括起來再與實際求和區相乘。如果缺少條件的外括弧將得到錯誤結果。2、如果不只兩個條件，也用同樣方式將所有條件羅列出來並相加。例如求區域中小於100或者等於120的數據和，那麼數組公式如下:=SUM(((G23:G32<100)+(G23:G32=110)+(G23:G32=120))*G23:G32)

4、多條件求和2：{=SUM((H34:H43="一車間")*(I34:I43="男")*J34:J43)}

[公式說明]：本公式以H、I兩列數據作為限制條件，對J列數據匯總。「(H34:H43="一車間")*(I34:I43="男")」表示同時滿足兩個條件，如果還有更多條件，也可以同時羅列出來，利用「*」符號連接，然後再與求和區相乘，得到最後的匯總結果。

[使用注意]：1、前三個案例是以求和數據自身為條件對目標區域進行求和，本案例以不同的區域作為限制，條件區有兩個，對第三區域進行求和。在設置公式時，三個區域的單元格個數必須一致。例如以下公式將產生錯誤結果:=SUM((H34:H43="一車間")*(I34:I44="男")*J34:J43)、=SUM((H34:H43="一車間")*(I35:I44="男")*J34:J43)。2、多條件求和時，條件錄用區域與求和區域可以在不同列，三個區域只需要寬度、高度一致即可。它們所在的列可以不同，起止行也可以不同，甚至三個區域可以分別位於不同的工作表中，仍然可以得到需要的結果。例如條件區在Sheet2工作表，求和區在當前工作表，公式可以修改為:=SUM((Sheet2!H34:H43="一車間")*(Sheet2!I34:I43="男")*J34:J43)。3、本公式可以用另一種簡化寫法，仍然可以返回正確結果:=SUM((H34:H43&I34:I43="一車間男")*J34:J43)

5、多工作表求和時表名稱快捷輸入：=SUM(附表一:附表五!B3:B10)

[公式說明]：對多表相同區域求和，不需要錄入每個工作表的求和地址，只需將第一個工作表名和最後一個表名用冒號連接，後跟"!"與單元格地址，以此作為SUM函數的參數即可。其中"!"表示前面的字元為工作表名稱。

[使用注意]：1、本方法僅適用於每個工作表地址一致的情況，否則只能逐個輸入地址再求和。2、多表求和，SUM函數的參數，工作表與單元格地址可以手動輸入，也可以通過選擇工作表中單元格地址來產生引用。具體步驟如下:輸入"=SUM("，然後選擇待匯總的第一個工作表B3:B10區域，按住【Shift】鍵再單擊工作表標籤中待求和的最後一個工作表名，最後按【Enter】鍵結果結束即可。3、如果工作表名中包含一些特殊字元，例如"!"、"@"，公式中的工作表名需要用單引號括起來，例如:=SUM("A組:!E組"!B3:B10)

6、快速設置當前表以外的所有工作表相同區域的總和公式：=SUM("*"!B3)

[公式說明]1、公式中兩個單引號中間加"*"表示除當前表以外的所有工作表。2、在輸入公式後，程序會自動將""*""轉換成實際的工作表名。當按下【Enter】鍵後，公式將變成:=SUM(附表一:附表五!B3)

[使用注意]1、""*""只能手動輸入，而不能用滑鼠選擇工作表來產生引用。2、公式中B3必須使用相對引用。3、本公式用法和"案例7"是不同的。在本例中雖然也可以用"案例7"的方法按住【Shift】鍵來選擇需要求和的工作表產生引用，但是操作煩瑣，特別是總表不在最後或者最前面，而是在待求和的工作表中間時，用"案例7"的方法需要選擇五次，而本例公式中的"*"可以自動的找到排除當前表之外的所有工作表數據。也就是說"總表"不管在任何位置，都可以用同樣的公式完成求和，而不需手動修改。4、公式中"*"左右的單引號必須是半形符號。

7、用SUM函數計數：{=SUM((H94:H102="男")*(I94:I102="漢"))}

[公式說明]：SUM函數雖然是求和函數，在很多時候可以用來計數。本例中利用"H94:H101="男""這一表達式返回一串包含TRUE和FALSE的數組，最後用"*1"將邏輯值轉換成數值，並用SUM函數匯總。

[使用注意]：1、表達式"H94:H101="男""得到的是包含邏輯值的數組。SUM函數可以對邏輯參數進行求和，但是包含於數組中的邏輯值值卻會被忽略。例如以下公式:=SUM(TRUE,1)—結果為2，=SUM({TRUE,1})結果為1。但是通過"*1"將邏輯值轉換成數值後就可以直接求和了。例如:=SUM({TRUE,1}*1)—結果為2。2、將邏輯值轉換成數值，除了用"*1"之外，也可以改用"--"或者"/1"等手法，但是用VALUE函數卻不行，它只能將文本型數字轉換成數值。例如:=SUM(--({TRUE,1}))—結果等於2；=SUM(({TRUE,1})/1)—結果等於1；=SUM(VALUE({TRUE,1}))—結果為錯誤值，因為VALUE函數無法轉換成TRUE。

8、求1累加到100之和：{=SUM(ROW(1:100))}

[公式說明]：本公式利用ROW函數返回1到100的序列，然後用SUM函數將這個序列匯總得到1—100的合計。

[使用注意]：1、ROW函數只能產生1到1048576之間的自然數(Excel2003版本為65536)，且不帶小數。但是通過換算也可以完成其他序列的求和。例如1/1、1/2、1/3、1/4……1/99、1/100之和。可以用以下公式:=SUM(1/ROW(1:100))。如果是求1開1次方，2開2次方，3開3次方至100開100次方的總和，那麼可以用以下數組公式:{=SUM(ROW(1:100)^(1/ROW(1:100)))}。如果是對1—100之間的奇數進行求和，可以用以下數組公式:{=SUM((ROW(1:50)*2-1))}。2、ROW(1:100)產生的結果是一個包含1到100之間的自然數數組。用SUM函數對這種數組求和時必須用數組公式，否則只能對數組中的第一個數值進行求和。如果將"ROW(1:100)"寫成"{1,2,3,4}"這種形式的數組則可以按照普通公式處理。

9、多個工作表相同區域求前三名產量之和：{=SUM(LARGE(CHOOSE({1,2,3,4,5},附表一!B3:B10,附表二!B3:B10,附表三!B3:B10,附表四!B3:B10,附表五!B3:B10),ROW(1:3)))}

[公式說明]：如以下公式:=SUM(LARGE(附表一:附表五!B3:B13,ROW(1:3)))。因為LARGE函數不支持多個工作表地址作為第一參數，即三維引用。所以本公式利用CHOOSE函數將三維引用轉換成二維數組後，就可以作為LARGE函數的參數進行運算了。用LARGE函數提取前三最大值後再用SUM函數匯總。

[使用注意]：公式的常量數組"{1,2,3,4,5}"可以轉換為"COLUMN(A:E)"，而不用"ROW(1:5)"。因為"ROW(1:5)"等於"{1;2;3;4;5})。區別為一個是橫向數組，一個是縱向數組。