學數組也有段時間，但盧子始終在數組的門外徘徊。聽到Ctrl+Shift+Enter三鍵猶如老鼠看到貓，但心中又存在幻想，希望有朝一日神人出現或者天降寶典，那時盧子就能夠輕鬆學好數組。但始終沒等到這一天，後來盧子借用玩遊戲的時間來學習數組公式，學了很久，忽然有一天發覺會了好多，真是意外的驚喜。
SUM函數看似簡單，實則蘊含高深用法。什麼是高手？高手就是能將最平常的函數變幻出神奇莫測的功能，解決掉你想都不敢想的問題。
Q：如果我們要求1-10的和怎麼辦？
A：可以通過輔助列，在A1-A10分別輸入1-10，然後用SUM函數求和，話說這個99%的人都會，如果你屬於那1%的群體，那恭喜你，你可以關閉這篇文章，該幹嘛幹嘛去。
=SUM(A1:A10)
不過如果數據龐大的話，如輸入1-10000，用這辦法恐怕行不通，你光輸入就得輸入到哭。那這時就可以通過ROW函數來生成1-N這樣的序號。
=SUM(ROW(1:10000))
這時習慣了普通公式的人，一回車就會發現問題，怎麼才1呢？
剛開始學習數組公式的時候，盧子就經常犯這種低級錯誤。記住了，數組公式需要按Ctrl+Shift+Enter三鍵結束才可以，否則出錯。三鍵一按，立馬返回50005000，可以用數學速算法驗證下，答案是正確的。
=(10000+1)*10000/2
這個公式雖好，但假如有某個人誤操作你的表格，將其中兩行刪除，這時你就傻眼了，公式自己會變。
看來這個公式並不完美，仍需改善，這時INDIRECT函數就派上用場
=SUM(ROW(INDIRECT("1:10000")))
不管你怎麼刪除行，效果始終不變。
加上INDIRECT函數就相當於引用一個固定的1:10000，而不用藉助單元格引用，直接用ROW(1:10000)其實還是要藉助單元格才行實現。
現在不討論這種萬一的情況，繼續聊新的話題。
Q：如果要求1-10奇數跟偶數又該如何？
A：其實我們可以利用MOD（數據,2），如果是奇數就是1，偶數就是0這個特點來處理。
奇數：
=SUM(MOD(ROW(1:10),2)*ROW(1:10))
偶數：
=SUM((MOD(ROW(1:10),2)=0)*ROW(1:10))
=SUM(MOD(ROW(1:10)-1,2)*ROW(1:10))
Q：如果要求1-10的最大，小5個數的和要怎麼做呢？
A：前幾大可以用LARGE函數，前幾小可以用SMALL函數。
前5大的和：
=SUM(LARGE(ROW(1:10),ROW(1:5)))
前5小的和：
=SUM(SMALL(ROW(1:10),ROW(1:5)))
LARGE跟SMALL函數的語法其實一樣，學一個就行。一起來看看LARGE函數的語法：
=LAGER（數字,N）
求A列最大跟第2大的數字，最大就是N是1，第2大就是N是2，依次類推。
SUM函數誰都知道可以求和，但他還可以取代SUMIF、SUMIFS函數實現條件求和，你知道嗎？當初就是被這一用法深深吸引，覺得SUM函數神奇，盧子才不知不覺喜歡上數組。閒話少說，進入主題。
某學校各人員成績，現在需要統計各學部的總成績？
正常的話用SUMIF函數就可以搞定，SUMIF就是條件求和的意思。
=SUMIF(E:E,G2,D:D)
其實我們可以將SUMIF拆分成兩個函數SUM+IF，先進行條件判斷，然後再求和。
以G2的學部財經為例，需要判斷的條件就是E列學部是不是等於財經，如果是就返回成績，不是就顯示0。選擇區域在K2:K16輸入公式，然後按Ctrl+Shift+Enter結束。可以清楚得看到所有符合財經的都返回本身的成績，不是財經的都返回0。
=IF(E2:E16=G2,D2:D16,0)
IF函數的區域判斷跟單元格單獨判斷是一樣的，其實這個公式等同於下面公式下拉的結果。唯一的差別在於，一個是選擇區域按三鍵結束，一個是下拉獲取結果而已。
=IF(E2=$G$2,D2,0)
到這一步已經完成了90%的工作，只需在最外面嵌套個SUM函數進行求和即可。
=SUM(IF($E$2:$E$16=G2,$D$2:$D$16))
後面的所有數組公式都是需要按三鍵結束，在這裡強調一下，為了方便說明，不再重複。
照著這個思路，看看SUM函數如何取代SUMIFS函數。現在增加了一個條件性別，變成多條件求和。
我們都知道在普通公式裡面AND就表示並且的意思，按常規想法已經是這樣設置公式
=IF(AND(E2:E16=G2,C2:C16=H2),D2:D16,0)
輸入公式後的結果讓人大跌眼鏡，居然全部都是0。
這時用常規公式，發現並沒有異常，怎麼回事呢？
=IF(AND(E2=$G$2,C2=$H$2),D2,0)
這時有「獨孤九劍」之稱的F9鍵就派上用場。
在編輯欄選擇AND部分，這時就會出現抹黑現象，然後F9鍵，得到FALSE，只有一個結果，也就是說只要有任何一個條件不滿足就是FALSE，這樣一來肯定有問題。
=IF(AND(E2:E16=G2,C2:C16=H2),D2:D16,0)
必須得到15個結果才行。那有沒有其他函數可以取代AND函數，答案是肯定的，那就是*。
=IF((E2:E16=G2)*(C2:C16=H2),D2:D16,0)
現在用F9鍵同樣對條件進行測試。
=IF((E2:E16=G2)*(C2:C16=H2),D2:D16,0)
得到這樣的結果：
=IF({1;0;0;1;0;0;0;1;0;0;0;0;0;0;0},D2:D16,0)
也就是說同時滿足兩個條件的返回1，否則返回0。1就是TRUE所以返回D列對應值，0就是FALSE所以返回0。如果對F9鍵還不熟練，沒關係，繼續回到單元格內進行測試，跟我們的判斷一致。
現在只需在最外面嵌套SUM函數即可。
=SUM(IF(($E$2:$E$16=G2)*($C$2:$C$16=H2),$D$2:$D$16))
通過*知道可以將各個條件連接起來，其實求和區域也可以看成一個條件，將公式變成：
=SUM(($E$2:$E$16=G2)*($C$2:$C$16=H2)*$D$2:$D$16)
這樣SUM函數條件求和的通用公式就出來了。
=SUM((條件1)*(條件2)*(條件3)*…*求和區域)
SUM函數不僅僅可以實現條件求和，還可以進行各種各樣的求和，下面通過一些例子來見識下。
數量是用VLOOKUP函數查詢引用過來，找不到對應值就顯示#N/A，如果直接求和的話出錯，該如何處理？
=SUM(A2:A11)
解決方案：
01 加一個條件來判斷數量是不是數字，就可以搞定。
=SUM(IF(ISNUMBER(A2:A11),A2:A11))
ISNUMBER函數只有一個參數，作用就是判定單元格是不是數字，如果是返回TRUE，否則返回FALSE。
IS類函數還有好多個，語法都差不多，有興趣的可以了解下。將滑鼠放在函數上面就有出現一些說明。
02 藉助高版本函數進行容錯，再求和會更簡單。
=SUM(IFERROR(A2:A11,0))
=SUM(IFNA(A2:A11,0))
這兩個函數的用法前面說過，這裡就不再重複解釋。
統計各季度的銷售額。
=SUM(IF(ISNUMBER(FIND("季度",A2:A17)),B2:B17))
SUM函數不能直接用*，但FIND函數也可以實現查找季度這2個字符是不是存在，如果存在就返回字符所在位置，否則返回錯誤值。
ISNUMBER函數判斷是否是數字，也就是包含季度，如果是返回B列的銷售額。
當然這裡只是為了說明用法，因為數據源本身就很有規律，每個季度都等於3個月的合計，所以也可以採取取巧法。
=SUM(B2:B17)/2
截止到目前SUM函數僅僅是取代SUMIF跟SUMIFS，還不能真正體現出價值，後面看看他如何實現別人做不到的事兒。
含有單位的人員銷售清單，直接求和得不出正確答案，怎麼才能讓含單位的銷售額可以求和呢？
幫助提到，SUM函數會自動忽略文本，600元這種就是文本，不屬於數字。最簡單的做法就是將元替換成空，然後自定義單元格格式G/通用格式"元"。很多人就是搞不明白一格一屬性的道理，才會造成匯總數據困難重重。正確的做法應該將元寫在欄位名那裡變成銷售額（元），這樣別人一看便知道。廢話了這麼多，進入正題，別見怪，只是有感而發而已。
剛才提到了替換這個詞，函數中也有屬於自己的替換函數，SUBSTITUTE函數。
SUBSTITUTE第四參數為可選，那就先別管他，其他參數可以理解為：
=SUBSTITUTE(文本,需要替換的舊字符,替換成新的字符)
單元格的元是多餘的，需要替換成空，空可以用""表示，替換成空後直接求和，可以嗎，不驗證猜一下？
=SUM(SUBSTITUTE(B2:B7,"元",""))
這個是數組公式，用法也跟前面差不多，目測應該可以匯總。
但實際SUBSTITUTE這個函數屬於文本函數，所以替換得到的數字，也屬於文本，在這裡叫做文本數字。數字有兩種類型，一種是文本數字，一種是真正的數字，即數值。數值是可以求和，而文本不能求和。如：賬簿上的數字跟牆上的數字是不同的，前者我們可以用這些數字進行各種分析，後者只能當欣賞用。那有什麼辦法還原數字的本質呢？
利用VALUE函數可以將文本型轉換成數值型。
=VALUE("600")
但一般情況下不會用這一種，而是通過運算轉換。
一起來了解「減負」運算。
在函數或公式中，運算過程會自動把文本轉換為數值(一個隱含過程)，再與數值進行運算，負值運算(-)也是一種運算，能把文本轉換成數值：
-"600"=-600
還記得負負得正吧？
-(-"600")=-(-600)=600
簡寫為：
--"600"=600
--可以把文本轉換為數值，但它不是標準的轉換方式，是借用負運算的隱含功能。
其實負負運算稱為減負運算更好，減去數字的負擔，還原數字的本質。
=SUM(--SUBSTITUTE(B2:B7,"元",""))
將這一部分用F9鍵抹黑，得到：
=SUM({600;120;1000;210;129;123})
這樣就能夠求和了。
綜上，最終的數組公式為：
=SUM(--SUBSTITUTE(B2:B7,"元",""))
如果對字符提取三兄弟有印象的話，還可以這樣設置公式，因為都是包含元，也可以利用總字符數-1這個特點來做
=SUM(--LEFT(B2:B7,LEN(B2:B7)-1))
最後再來一個高段黑的，錄入金額的時候，居然把姓名也寫在一起。
看到這個，盧子都頭大，奉勸大家一句，別把Excel當Word使用，否則後果很嚴重。
不過即使困難重重，SUM函數也能一一拿下，沒有求不了和的數據，只有求不了和的人。
我們知道LEN函數可以統計字符數，其實他還有一個兄弟叫LENB函數，他是統計位元組數。漢字2位元組，數字1位元組。利用這個特點我們可以得出：
漢字的個數：
=LENB-LEN
數字的個數：
=LEN-漢字的個數=LEN-(LENB-LEN)=2*LEN-LENB
最終公式：
=SUM(--RIGHT(A2:A5,2*LEN(A2:A5)-LENB(A2:A5)))
SUM函數能實現各種各樣求和那也是情理之中，如果說SUM函數能取代COUNTIF、COUNTIFS函數實現條件計數，也許很多人會覺得天方夜譚。SUM函數強大得難以想像，很多人掌握的知識只是冰山一角。
提取姓名中的第一個字符也就是姓氏，可以用LEFT函數。
=LEFT(A2,1)
第二參數省略不寫的話，默認就是提取1位，所以可簡化為：
=LEFT(A2)
結合前面SUM+IF的用法可用：
=SUM(IF(LEFT(A$2:A$12)=D2,1))
讓符合條件的顯示1，然後嵌套SUM進行求和。
統計各學部男女的人數。
利用SUM+IF的做法，依樣畫葫蘆也能做到，但這裡不做說明。大家是否還記得SUM函數求和的通用公式：
=SUM((條件1)*(條件2)*(條件3)*…*求和區域)
其實省略掉求和區域就是條件計數的通用公式：
=SUM((條件1)*(條件2)*(條件3)*…*(條件n))
有了這個通用公式，條件計數so easy！
=SUM(($E$2:$E$16=$G2)*($C$2:$C$16=H$1))
計數還有一個經典的問題，就是不重複計數。統計不重複學部的個數。
計算區域不重複個數的經典公式，需要好好理解。
=SUM(1/COUNTIF(區域,區域))
=SUM(1/COUNTIF(E2:E16,E2:E16))
如果區域很多的話，可以改小，這樣便於理解。
觀察
=SUM(1/COUNTIF(E2:E16,E2:E16))
F9鍵抹黑
=SUM(1/{4;4;3;4;4;4;4;4;4;4;4;4;3;4;3})
Esc鍵返回
=SUM(1/COUNTIF(E2:E16,E2:E16))
F9鍵抹黑
=SUM({0.25;0.25;0.333333333333333;0.25;0.25;0.25;0.25;0.25;0.25;0.25;0.25;0.25;0.333333333333333;0.25;0.333333333333333})
Esc鍵返回，在單元格按三建結束看到結果：4。
分析
F9鍵觀察有時並不太直觀，回到工作表中繼續看看。
=COUNTIF(E2:E16,E2:E16)是多單元格數組，等同於=COUNTIF(E$2:E$16,E2)下拉的結果，也就是統計每個單元格本身出現的次數，如1。
=1/COUNTIF(E2:E16,E2:E16)是多單元格數組，等同於=1/ COUNTIF(E$2:E$16,E2)下拉的結果，也就是1/每個單元格本身出現的次數，為了讓數據更直觀轉換成分數形式，如2。
出現4次就變成1/4，出現3次就變成1/3。1/3+1/3+1/3=3*(1/3)=1，1/N+…+1/N=N*(1/N)=1，不管出現幾次，相加都等於1。
最後將這些相加就是得到不重複的數量，如3。
解讀公式的一些習慣：
1.把區域改小，這樣便於查看，如A1:A1000改成A1:A9。
2. F9鍵配合Ctrl+Z或者Esc鍵不斷地看運算過程再返回，重複到理解為止。
3. 輸入公式回到單元格查看運算過程，這種相對比較直觀。
4. 分析。
第2，3點可選，看你對公式的熟練程度，如果不熟練選擇3，熟練的話選擇2。
學好SUM函數不過是為了打開數組之門，知道數組的一些基礎用法，要學好數組還需要更多的知識支撐才可以。
推薦：每個Excel學得好的人都有一段不為人知的往事，我有故事，你想聽嗎
上篇：驚訝！簡單的Excel圖標集，居然難倒50%的人
每個人都是從不懂到懂，多花點時間和精力，用心學習，你也可以學得很好。嘗試從學習中找到樂趣，培養自己的興趣愛好，這樣會學得更快。
在學習Excel的過程中，你有沒發現什麼有趣的事兒？
作者：盧子，清華暢銷書作者，《Excel效率手冊 早做完，不加班》系列叢書創始人，個人公眾號：Excel不加班（ID：Excelbujiaban）
像玩遊戲一樣玩SUM函數。
SUM函數看似簡單，實則蘊含高深用法。什麼是高手？高手就是能將最平常的函數變幻出神奇莫測的功能，解決掉你想都不敢想的問題。
1.閒聊SUM函數
Q：如果我們要求1-10的和怎麼辦？
A：可以通過輔助列，在A1-A10分別輸入1-10，然後用SUM函數求和，話說這個99%的人都會，如果你屬於那1%的群體，那恭喜你，你可以關閉這篇文章，該幹嘛幹嘛去。
=SUM(A1:A10)
不過如果數據龐大的話，如輸入1-10000，用這辦法恐怕行不通，你光輸入就得輸入到哭。那這時就可以通過ROW函數來生成1-N這樣的序號。
=SUM(ROW(1:10000))
這時習慣了普通公式的人，一回車就會發現問題，怎麼才1呢？
剛開始學習數組公式的時候，盧子就經常犯這種低級錯誤。記住了，數組公式需要按Ctrl+Shift+Enter三鍵結束才可以，否則出錯。三鍵一按，立馬返回50005000，可以用數學速算法驗證下，答案是正確的。
=(10000+1)*10000/2
這個公式雖好，但假如有某個人誤操作你的表格，將其中兩行刪除，這時你就傻眼了，公式自己會變。
看來這個公式並不完美，仍需改善，這時INDIRECT函數就派上用場
=SUM(ROW(INDIRECT("1:10000")))
不管你怎麼刪除行，效果始終不變。
加上INDIRECT函數就相當於引用一個固定的1:10000，而不用藉助單元格引用，直接用ROW(1:10000)其實還是要藉助單元格才行實現。
現在不討論這種萬一的情況，繼續聊新的話題。
Q：如果要求1-10奇數跟偶數又該如何？
A：其實我們可以利用MOD（數據,2），如果是奇數就是1，偶數就是0這個特點來處理。
奇數：
=SUM(MOD(ROW(1:10),2)*ROW(1:10))
偶數：
=SUM((MOD(ROW(1:10),2)=0)*ROW(1:10))
=SUM(MOD(ROW(1:10)-1,2)*ROW(1:10))
Q：如果要求1-10的最大，小5個數的和要怎麼做呢？
A：前幾大可以用LARGE函數，前幾小可以用SMALL函數。
前5大的和：
=SUM(LARGE(ROW(1:10),ROW(1:5)))
前5小的和：
=SUM(SMALL(ROW(1:10),ROW(1:5)))
LARGE跟SMALL函數的語法其實一樣，學一個就行。一起來看看LARGE函數的語法：
=LAGER（數字,N）
求A列最大跟第2大的數字，最大就是N是1，第2大就是N是2，依次類推。
2.求和問題一次全搞定
SUM函數誰都知道可以求和，但他還可以取代SUMIF、SUMIFS函數實現條件求和，你知道嗎？當初就是被這一用法深深吸引，覺得SUM函數神奇，盧子才不知不覺喜歡上數組。閒話少說，進入主題。
某學校各人員成績，現在需要統計各學部的總成績？
正常的話用SUMIF函數就可以搞定，SUMIF就是條件求和的意思。
=SUMIF(E:E,G2,D:D)
其實我們可以將SUMIF拆分成兩個函數SUM+IF，先進行條件判斷，然後再求和。
以G2的學部財經為例，需要判斷的條件就是E列學部是不是等於財經，如果是就返回成績，不是就顯示0。選擇區域在K2:K16輸入公式，然後按Ctrl+Shift+Enter結束。可以清楚得看到所有符合財經的都返回本身的成績，不是財經的都返回0。
=IF(E2:E16=G2,D2:D16,0)
IF函數的區域判斷跟單元格單獨判斷是一樣的，其實這個公式等同於下面公式下拉的結果。唯一的差別在於，一個是選擇區域按三鍵結束，一個是下拉獲取結果而已。
=IF(E2=$G$2,D2,0)
到這一步已經完成了90%的工作，只需在最外面嵌套個SUM函數進行求和即可。
=SUM(IF($E$2:$E$16=G2,$D$2:$D$16))
後面的所有數組公式都是需要按三鍵結束，在這裡強調一下，為了方便說明，不再重複。
照著這個思路，看看SUM函數如何取代SUMIFS函數。現在增加了一個條件性別，變成多條件求和。
我們都知道在普通公式裡面AND就表示並且的意思，按常規想法已經是這樣設置公式
=IF(AND(E2:E16=G2,C2:C16=H2),D2:D16,0)
輸入公式後的結果讓人大跌眼鏡，居然全部都是0。
這時用常規公式，發現並沒有異常，怎麼回事呢？
=IF(AND(E2=$G$2,C2=$H$2),D2,0)
這時有「獨孤九劍」之稱的F9鍵就派上用場。
在編輯欄選擇AND部分，這時就會出現抹黑現象，然後F9鍵，得到FALSE，只有一個結果，也就是說只要有任何一個條件不滿足就是FALSE，這樣一來肯定有問題。
=IF(AND(E2:E16=G2,C2:C16=H2),D2:D16,0)
必須得到15個結果才行。那有沒有其他函數可以取代AND函數，答案是肯定的，那就是*。
=IF((E2:E16=G2)*(C2:C16=H2),D2:D16,0)
現在用F9鍵同樣對條件進行測試。
=IF((E2:E16=G2)*(C2:C16=H2),D2:D16,0)
得到這樣的結果：
=IF({1;0;0;1;0;0;0;1;0;0;0;0;0;0;0},D2:D16,0)
也就是說同時滿足兩個條件的返回1，否則返回0。1就是TRUE所以返回D列對應值，0就是FALSE所以返回0。如果對F9鍵還不熟練，沒關係，繼續回到單元格內進行測試，跟我們的判斷一致。
現在只需在最外面嵌套SUM函數即可。
=SUM(IF(($E$2:$E$16=G2)*($C$2:$C$16=H2),$D$2:$D$16))
通過*知道可以將各個條件連接起來，其實求和區域也可以看成一個條件，將公式變成：
=SUM(($E$2:$E$16=G2)*($C$2:$C$16=H2)*$D$2:$D$16)
這樣SUM函數條件求和的通用公式就出來了。
=SUM((條件1)*(條件2)*(條件3)*…*求和區域)
SUM函數不僅僅可以實現條件求和，還可以進行各種各樣的求和，下面通過一些例子來見識下。
數量是用VLOOKUP函數查詢引用過來，找不到對應值就顯示#N/A，如果直接求和的話出錯，該如何處理？
=SUM(A2:A11)
解決方案：
01 加一個條件來判斷數量是不是數字，就可以搞定。
=SUM(IF(ISNUMBER(A2:A11),A2:A11))
ISNUMBER函數只有一個參數，作用就是判定單元格是不是數字，如果是返回TRUE，否則返回FALSE。
IS類函數還有好多個，語法都差不多，有興趣的可以了解下。將滑鼠放在函數上面就有出現一些說明。
02 藉助高版本函數進行容錯，再求和會更簡單。
=SUM(IFERROR(A2:A11,0))
=SUM(IFNA(A2:A11,0))
這兩個函數的用法前面說過，這裡就不再重複解釋。
統計各季度的銷售額。
=SUM(IF(ISNUMBER(FIND("季度",A2:A17)),B2:B17))
SUM函數不能直接用*，但FIND函數也可以實現查找季度這2個字符是不是存在，如果存在就返回字符所在位置，否則返回錯誤值。
ISNUMBER函數判斷是否是數字，也就是包含季度，如果是返回B列的銷售額。
當然這裡只是為了說明用法，因為數據源本身就很有規律，每個季度都等於3個月的合計，所以也可以採取取巧法。
=SUM(B2:B17)/2
截止到目前SUM函數僅僅是取代SUMIF跟SUMIFS，還不能真正體現出價值，後面看看他如何實現別人做不到的事兒。
含有單位的人員銷售清單，直接求和得不出正確答案，怎麼才能讓含單位的銷售額可以求和呢？
幫助提到，SUM函數會自動忽略文本，600元這種就是文本，不屬於數字。最簡單的做法就是將元替換成空，然後自定義單元格格式G/通用格式"元"。很多人就是搞不明白一格一屬性的道理，才會造成匯總數據困難重重。正確的做法應該將元寫在欄位名那裡變成銷售額（元），這樣別人一看便知道。廢話了這麼多，進入正題，別見怪，只是有感而發而已。
剛才提到了替換這個詞，函數中也有屬於自己的替換函數，SUBSTITUTE函數。
SUBSTITUTE第四參數為可選，那就先別管他，其他參數可以理解為：
=SUBSTITUTE(文本,需要替換的舊字符,替換成新的字符)
單元格的元是多餘的，需要替換成空，空可以用""表示，替換成空後直接求和，可以嗎，不驗證猜一下？
=SUM(SUBSTITUTE(B2:B7,"元",""))
這個是數組公式，用法也跟前面差不多，目測應該可以匯總。
但實際SUBSTITUTE這個函數屬於文本函數，所以替換得到的數字，也屬於文本，在這裡叫做文本數字。數字有兩種類型，一種是文本數字，一種是真正的數字，即數值。數值是可以求和，而文本不能求和。如：賬簿上的數字跟牆上的數字是不同的，前者我們可以用這些數字進行各種分析，後者只能當欣賞用。那有什麼辦法還原數字的本質呢？
利用VALUE函數可以將文本型轉換成數值型。
=VALUE("600")
但一般情況下不會用這一種，而是通過運算轉換。
一起來了解「減負」運算。
在函數或公式中，運算過程會自動把文本轉換為數值(一個隱含過程)，再與數值進行運算，負值運算(-)也是一種運算，能把文本轉換成數值：
-"600"=-600
還記得負負得正吧？
-(-"600")=-(-600)=600
簡寫為：
--"600"=600
--可以把文本轉換為數值，但它不是標準的轉換方式，是借用負運算的隱含功能。
其實負負運算稱為減負運算更好，減去數字的負擔，還原數字的本質。
=SUM(--SUBSTITUTE(B2:B7,"元",""))
將這一部分用F9鍵抹黑，得到：
=SUM({600;120;1000;210;129;123})
這樣就能夠求和了。
綜上，最終的數組公式為：
=SUM(--SUBSTITUTE(B2:B7,"元",""))
如果對字符提取三兄弟有印象的話，還可以這樣設置公式，因為都是包含元，也可以利用總字符數-1這個特點來做
=SUM(--LEFT(B2:B7,LEN(B2:B7)-1))
最後再來一個高段黑的，錄入金額的時候，居然把姓名也寫在一起。
看到這個，盧子都頭大，奉勸大家一句，別把Excel當Word使用，否則後果很嚴重。
不過即使困難重重，SUM函數也能一一拿下，沒有求不了和的數據，只有求不了和的人。
我們知道LEN函數可以統計字符數，其實他還有一個兄弟叫LENB函數，他是統計位元組數。漢字2位元組，數字1位元組。利用這個特點我們可以得出：
漢字的個數：
=LENB-LEN
數字的個數：
=LEN-漢字的個數=LEN-(LENB-LEN)=2*LEN-LENB
最終公式：
=SUM(--RIGHT(A2:A5,2*LEN(A2:A5)-LENB(A2:A5)))
3.SUM函數其實也能計數
SUM函數能實現各種各樣求和那也是情理之中，如果說SUM函數能取代COUNTIF、COUNTIFS函數實現條件計數，也許很多人會覺得天方夜譚。SUM函數強大得難以想像，很多人掌握的知識只是冰山一角。
提取姓名中的第一個字符也就是姓氏，可以用LEFT函數。
=LEFT(A2,1)
第二參數省略不寫的話，默認就是提取1位，所以可簡化為：
=LEFT(A2)
結合前面SUM+IF的用法可用：
=SUM(IF(LEFT(A$2:A$12)=D2,1))
讓符合條件的顯示1，然後嵌套SUM進行求和。
統計各學部男女的人數。
利用SUM+IF的做法，依樣畫葫蘆也能做到，但這裡不做說明。大家是否還記得SUM函數求和的通用公式：
=SUM((條件1)*(條件2)*(條件3)*…*求和區域)
其實省略掉求和區域就是條件計數的通用公式：
=SUM((條件1)*(條件2)*(條件3)*…*(條件n))
有了這個通用公式，條件計數so easy！
=SUM(($E$2:$E$16=$G2)*($C$2:$C$16=H$1))
計數還有一個經典的問題，就是不重複計數。統計不重複學部的個數。
計算區域不重複個數的經典公式，需要好好理解。
=SUM(1/COUNTIF(區域,區域))
=SUM(1/COUNTIF(E2:E16,E2:E16))
如果區域很多的話，可以改小，這樣便於理解。
觀察
=SUM(1/COUNTIF(E2:E16,E2:E16))
F9鍵抹黑
=SUM(1/{4;4;3;4;4;4;4;4;4;4;4;4;3;4;3})
Esc鍵返回
=SUM(1/COUNTIF(E2:E16,E2:E16))
F9鍵抹黑
=SUM({0.25;0.25;0.333333333333333;0.25;0.25;0.25;0.25;0.25;0.25;0.25;0.25;0.25;0.333333333333333;0.25;0.333333333333333})
Esc鍵返回，在單元格按三建結束看到結果：4。
分析
F9鍵觀察有時並不太直觀，回到工作表中繼續看看。
=COUNTIF(E2:E16,E2:E16)是多單元格數組，等同於=COUNTIF(E$2:E$16,E2)下拉的結果，也就是統計每個單元格本身出現的次數，如1。
=1/COUNTIF(E2:E16,E2:E16)是多單元格數組，等同於=1/ COUNTIF(E$2:E$16,E2)下拉的結果，也就是1/每個單元格本身出現的次數，為了讓數據更直觀轉換成分數形式，如2。
出現4次就變成1/4，出現3次就變成1/3。1/3+1/3+1/3=3*(1/3)=1，1/N+…+1/N=N*(1/N)=1，不管出現幾次，相加都等於1。
最後將這些相加就是得到不重複的數量，如3。
解讀公式的一些習慣：
1.把區域改小，這樣便於查看，如A1:A1000改成A1:A9。
2. F9鍵配合Ctrl+Z或者Esc鍵不斷地看運算過程再返回，重複到理解為止。
3. 輸入公式回到單元格查看運算過程，這種相對比較直觀。
4. 分析。
第2，3點可選，看你對公式的熟練程度，如果不熟練選擇3，熟練的話選擇2。
學好SUM函數不過是為了打開數組之門，知道數組的一些基礎用法，要學好數組還需要更多的知識支撐才可以。
推薦：每個Excel學得好的人都有一段不為人知的往事，我有故事，你想聽嗎
上篇：驚訝！簡單的Excel圖標集，居然難倒50%的人
每個人都是從不懂到懂，多花點時間和精力，用心學習，你也可以學得很好。嘗試從學習中找到樂趣，培養自己的興趣愛好，這樣會學得更快。
在學習Excel的過程中，你有沒發現什麼有趣的事兒？
作者：盧子，清華暢銷書作者，《Excel效率手冊 早做完，不加班》系列叢書創始人，個人公眾號：Excel不加班（ID：Excelbujiaban）
收藏