跟我一起，穿越時間！

回顧一下，經過前面6期的連載，相信你的Excel水平已經有了肉眼可見的提高，Excel基礎理論也變得紮實穩固，俗話說“基礎不牢，地動山搖”，在正式實際應用之前，如果你還沒有仔細看過前面的連載，可以點擊頭像或鍊接跳轉：

穿越時間•Excel升級之路連載1：Office2010安裝體驗及個性設置

穿越時間•Excel升級之路連載2：打牢Excel 2010中的數據類型基礎

穿越時間•Excel升級之路連載3：數據類型檢測與相互轉換技巧

穿越時間•Excel升級之路連載4：精通公式運算規則和字符比較

穿越時間•Excel升級之路連載5：數組的理想照進現實

穿越時間·Excel升級之路連載6：神級函數SUMPRODUCT的驚鴻一瞥

連載6中我們提到了神級函數SUMPRODUCT，它本身支持數組運算，當然不是數組的單值運算也不在話下，神級函數有多神？如果以我們生活的三維空間舉例，sumproduct堪稱四維空間的高級文明，對我們的日常應用實屬降維打擊。今天我們要讨論的是Excel中的條件計數，分為四個部分（單條件計數、單條件計數的和；多條件計數，多條件計數的和），每個部分我都會讨論十餘種解決方案！

一起走到Excel升級之路連載7：SUMPRODUCT函數挑戰Excel條件計數。

條件計數是什麼？相信大家都不陌生，就是按條件統計數目，或者是統計符合一個條件或多個條件的單元格的個數。它是條件基礎上的計數。

在Excel中，能夠實現條件計數的方法非常多，各種方法可一覽如下。

1、COUNT函數

Excel中可以找到5個和計數相關的函數，他們都以count開頭。

前三個count、counta、countblank是最基本的計數函數，我們可以用它們來計算區域中包含數字的單元格個數、非空單元格個數和空單元格個數。

後兩個countif、countifs是單條件計數函數和多條件計數函數，通過他們，我們可以得到滿足某個條件或滿足某幾個條件的單元格數量。

2、SUM函數

SUM函數隻是求和函數嗎？不要以為sum函數是不起眼的小學生函數，整合我們前面連載講過的運算順序和數組之後，sum函數也可以用來進行條件計數。

3、sumproduct函數

既然名聲在外，那必須實至名歸，sumproduct說還有什麼我不能計算的呢。

4、篩選

篩選方法是人工方法，如果說上面的各種函數方法是工業文明流水線，那麼篩選方法則是農業文明小農經濟精耕細作，但是通過篩選來進行條件計數也最直觀的，可謂所見即所得。

下面，我們一起開始條件計數的挑戰。

我們延續連載6中武林各派銷售文創産品的情境，本系列連載中的銷售情境數據均為演示學習需要而原創虛構。

下表包括不同的門派、銷售人員和文創産品，同時列出來的還有銷售數量和單價情況。

在銷售文創産品的過程中，周婉悅成功開單的日期共有幾天？

（1）方法一：篩選法

表格中每一行都代表一天開單日期，想知道周婉悅成功開單的日期有幾天，即周婉悅開了幾次單，隻需在D列銷售人員列篩選“周婉悅”即可。

我們可以非常直觀地看到周婉悅成功開單的日期有3天。

為了實現篩選後Excel自動計數，注意我在D21單元格中使用了函數公式，來實現篩選後的自動更新計算，并展示結果。公式為：

=SUBTOTAL(2,H2:H18)

SUBTOTAL用于分類彙總，函數可以返回列表或數據庫中的分類彙總，語法是：SUBTOTAL(function_num,ref1,[ref2],...])

其第一個參數指定彙總函數，例如求平均值、計數、求和等，後面的參數則為計算區域，因為現在要數出篩選後符合條件的數目，所以使用的是計數功能，第一個代碼指定為2，計算區域為銷售人員H2:H18，這樣我們進行篩選，即可實現實時根據篩選結果自動計算。關于這個函數的更多用法，這裡不展開。

（2）方法二：COUNTIF函數

通過countif可以計算某個區域中滿足給定條件的單元格數目，在這裡很顯然就是計算銷售人員區域D2:D18中是“周婉悅”的單元格數目。

Countif函數非常簡單，COUNTIF(range, criteria)，第一個參數是區域範圍，第二個參數是條件，注意條件中要使用英文引号，輸入文本字符型的周婉悅或者引用包含周婉悅的單元格。

=COUNTIF(D2:D18,"周婉悅")

=COUNTIF(D2:D18,D2)

我們可以看到函數直接計算出來結果是3

當然，這裡使用countifs也是可以的，不過因為這一部分是單條件計數，所以沒有必要使用countifs。

（3）方法三：SUM函數

Sum函數也能數出周婉悅成功開單的日期數？來看能否理解下面的公式：

=SUM((D2:D18="周婉悅")*1)

=SUM((D2:D18=D2)*1)

按下Ctrl Shift Enter執行數組運算，公式的結果也是3，為什麼？

如果看不明白公式的意思，說明前面連載修煉還不到位，請返回繼續學習。

解釋：

D2:D18=D2實質為一個邏輯判斷，因為是數組運算，所以會依次判斷D2:D18中每一個單元格裡的銷售人員是否是“周婉悅”（D2=D2嗎、D3=D2嗎、D4=D2嗎……），如果是則返回邏輯值TRUE，如果不是則返回邏輯值FALSE

由此構成一個數組，TRUE就代表一個周婉悅成功開單的日子；

那麼隻需要知道數組中TRUE有幾個就行了。

(D2:D18=D2)*1

為什麼要*1，原因是sum函數會忽略計算完成的單個數組參數中的邏輯值，無論TRUE還是FALSE都會被當做0（這點我在連載5、連載6讨論過），所以需要用*1的方法将邏輯值轉換為0或1

這樣數組就變為{1;0;1;0;0……0}

數組中的1就代表一個成功開單的日子；

然後sum函數對這個數組求和，實質是把數組裡所有的元素相加求和，有多少個1就有多少個成功開單的日子。

這樣，sum函數和數組一結合也實現了單條件計數。

（4）方法四：sumproduct函數

既然是sum函數可以完成的事，那sumproduct函數自然毫無問題，而且更加簡潔。

=SUMPRODUCT((D2:D18="周婉悅")*1)

=SUMPRODUCT((D2:D18=D2)*1) 按Enter鍵即可

原理和上面利用sum函數的計算并無太多差異，

D2:D18=D2判斷D2:D18中銷售人員是否是“周婉悅”，返回邏輯值構成的數組；

(D2:D18=D2)*1把返回的邏輯值轉變為數值（SUMPRODUCT函數也會忽略計算完成的單一數組參數中的邏輯值，這在連載6中讨論過）；

最後由于sumproduct可以直接處理數組，因此按Enter鍵即可計算。

我們升一下級，現在求一下周婉悅和玉玲師太成功開單的日子共有幾天？

這可以理解為單條件計數的和的問題。

（1）方法一：篩選法

周婉悅和玉玲師太成功開單的日子，隻需在D列銷售人員中篩選“周婉悅”和“玉玲師太”即可。

我們可以看到答案是4天。

（2）方法二：countif函數

求周婉悅和玉玲師太成功開單的日子共有幾天，就是求周婉悅成功開單的日子 玉玲師太成功開單的日子，那麼公式可以寫成：

=COUNTIF(D2:D18,"周婉悅") COUNTIF(D2:D18,"玉玲師太")

=COUNTIF(D2:D18,D2) COUNTIF(D2:D18,D18)

結果為4

這種分寫累加的方法很容易理解，但是存在限制，我們繼續往下看。

（3）方法三：sum函數

按照上面countif函數的思路，把周婉悅成功開單的日子和玉玲師太成功開單的日子加起來，使用sum函數法可以寫為：

分寫累加：周婉悅成功開單的日子 玉玲師太成功開單的日子

=SUM((D2:D18="周婉悅")*1) SUM((D2:D18="玉玲師太")*1)

=SUM((D2:D18=D2)*1) SUM((D2:D18=D18)*1)

按Ctrl Shift Enter計算，結果為4

或者換種思路，考慮為求滿足周婉悅或玉玲師太成功開單的日子：

=SUM(((D2:D18="周婉悅") (D2:D18="玉玲師太"))*1)

=SUM(((D2:D18=D2) (D2:D18=D18))*1)

=SUM((D2:D18="周婉悅") (D2:D18="玉玲師太"))

=SUM((D2:D18=D2) (D2:D18=D18))

以上為SUM數組方法中的單參數寫法，加号 體現或的意思（連載4精通公式運算規則和字符比較中有詳細介紹）。

但如果現在要求周婉悅、玉玲師太、瓊英、王孫藥師……等等n個人成功開單的日子，分寫累加的方法就不太合适了，因此，借助數組，我們有了一種合寫的辦法：

合寫：

=SUM((D2:D18={"周婉悅","玉玲師太"})*1) 按Ctrl Shift Enter計算

這裡用到的是異向一維數組的邏輯運算，構建二維數組的辦法，如果不理解請複習前面的連載内容。

解釋一下：

D2:D18={"周婉悅","玉玲師太"} D2:D18是一個由銷售人員構成的縱向一維數組，{"周婉悅","玉玲師太"}是由目标條件周婉悅和玉玲師太構成的橫向一維數組，二者進行邏輯判斷，返回一個由邏輯值構成的二維數組（這是數組的運算規則，連載5數組的理想照進現實中詳細講過）；

這個由邏輯值構成的二維數組裡面的TRUE即代表周婉悅或玉玲師太成功開單的日子，求出TRUE的個數即可。

那麼由于sum忽略單參數數組中邏輯值得緣故，所以需要(D2:D18={"周婉悅","玉玲師太"})*1轉化一下數據類型，然後通過sum計算1的個數就是答案。

注意=SUM((D2:D18={"周婉悅","玉玲師太"})*1)中我使用的是常量數組{"周婉悅","玉玲師太"}，而不是單元格引用（D2和D18并不是相鄰的），如果寫成=SUM((D2:D18={D2,D18})*1) 則錯誤。大括号{}内表示常量數組，不能放入單元格地址。

如果要使用單元格引用，則必須是橫向連續的“周婉悅”“玉玲師太”兩個單元格。

有的人說這裡就所需要準備輔助列了，例如借用上圖K1、L1單元格寫成：=SUM((D2:D18=K1:L1)*1)

其實不必，我們這種合寫的辦法還可以再次升級到更高級的層面（如果你仔細看過連載5：數組的理想照進現實，你會想到構建虛拟内存數組）

合寫再進一步，虛拟構建：

=SUM((D2:D18=IF({1,0},D2,D18))*1) 按Ctrl Shift Enter計算

利用If函數、數組{1,0}把不相鄰的D2和D18構建為橫向的内存數組，避免直接寫出常量數組{"周婉悅","玉玲師太"}

=SUM((D2:D18=CHOOSE({1,2},D2,D18))*1) 按Ctrl Shift Enter計算

利用Choose函數、數組{1,2}把不相鄰的D2和D18構建為橫向的内存數組，避免直接寫出常量數組{"周婉悅","玉玲師太"}

以上兩種方法屬于構建的高級用法，如果不理解，請仔細閱讀之前的連載内容。

（3）方法四：sumproduct函數

如果上面sum函數的各種情況都能夠理解的話，那麼換用sumproduct函數将會更加簡潔高效。

分寫累加：

周婉悅成功開單的日子 玉玲師太成功開單的日子

=SUMPRODUCT((D2:D18="周婉悅")*1) SUMPRODUCT((D2:D18="玉玲師太")*1)

=SUMPRODUCT((D2:D18=D2)*1) SUMPRODUCT((D2:D18=D18)*1)

或者考慮為求滿足周婉悅或玉玲師太成功開單的日子：

=SUMPRODUCT((D2:D18="周婉悅") (D2:D18="玉玲師太"))

=SUMPRODUCT((D2:D18=D2) (D2:D18=D18))

合寫：

=SUMPRODUCT((D2:D18={"周婉悅","玉玲師太"})*1)

同樣，這裡也用到了異向一維數組的邏輯運算構建二維數組，如果不理解請複習前面的内容。

合寫再進一步，虛拟構建：

=SUMPRODUCT((D2:D18=IF({1,0},D2,D18))*1) 把不相鄰的D2和D18構建内存數組，避免直接寫出常量數組{"周婉悅","玉玲師太"}

=SUMPRODUCT((D2:D18=CHOOSE({1,2},D2,D18))*1) 把不相鄰的D2和D18構建内存數組，避免直接寫出常量數組{"周婉悅","玉玲師太"}

最終sumproduct函數完成條件計數、條件計數的和就是這麼簡潔，你學會了嗎？上面共有十餘種不同的方法，如果沒有問題，就可以繼續整體升級到下一部分。

首先需要清楚多條件計數不等同于單條件計數的和！

單條件計數的和：條件是并列的，“或”的意思，滿足其一即是；

多條件計數：條件是遞進的，“并且”的意思，都要同時滿足。

問：周婉悅成功開單賣出玉香劍的日期共有幾天？

（1）方法一：篩選

問周婉悅成功開單賣出玉香劍的日期數，要求銷售人員滿足“周婉悅”，同時文創産品滿足“玉香劍”；

隻要在D列銷售人員中篩選“周婉悅”，然後在E列“文創産品”中篩選“玉香劍”即可。

共有2天

（2）方法二：countifs多條件計數

銷售人員得是“周婉悅”，周婉悅賣出的東西得是“玉香劍”，這是一種遞進的2個限制條件，可以通過countifs函數來實現。

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

COUNTIFS(區域1，區域1要滿足的條件，區域2，區域2要滿足的條件……)

=COUNTIFS(D2:D18,"周婉悅",E2:E18,"玉香劍")

=COUNTIFS(D2:D18,D2,E2:E18,E2)

這個其實非常簡單。

（3）方法三：SUM函數

SUM函數其實也能做到的：

=SUM((D2:D18="周婉悅")*(E2:E18="玉香劍"))

=SUM((D2:D18=D2)*(E2:E18=E2))

按Ctrl Shift Enter計算

(D2:D18=D2)、(E2:E18=E2)分别為兩個條件判斷，執行數組計算後返回兩個由邏輯值構成的數組，二者相乘，即可以滿足同時找出“周婉悅”賣“玉香劍”的情況，又可以實現邏輯值到數值型值的轉換。

因此這裡沒有必要寫成以下形式：=SUM(((D2:D18=D2)*1)*((E2:E18=E2)*1)) 如果不理解請看前面的内容。

下圖可以體現，隻有周婉悅賣玉香劍，兩者都為TRUE的，最後相乘得到1才會被最終計數；

周婉悅賣其他東西或者其他人賣玉香劍，都會被某一個FALSE在乘法運算中清零，不會被最終計數。

（4）SUMproduct函數則加簡潔

多參數寫法：

=SUMPRODUCT((D2:D18="周婉悅")*1,(E2:E18="玉香劍")*1)

=SUMPRODUCT((D2:D18=D2)*1,(E2:E18=E2)*1)

(D2:D18=D2)*1和(E2:E18=E2)*1分别作為sumproduct的兩個參數，分别找出“周婉悅”賣出東西的日子和其他人賣出“玉香劍”的日子，并通過*1将邏輯值數組轉換為數值型的0或1，然後sumproduct函數将兩個數組求積再求和，得到答案。

需要注意的是，如果寫成：=SUMPRODUCT(D2:D18=D2,E2:E18=E2)，結果則為0，這是不對的，為什麼？因為這種情況下sumproduct會把D2:D18=D2和E2:E18=E2返回的由邏輯值構成的數組（非數值元素）視為0，如果不理解請看前面的内容。

運算過程：

單參數寫法

此外，如果利用=SUMPRODUCT((D2:D18=D2)*(E2:E18=E2))，單參數計算，也是可以的。

這裡可以再考慮考慮sumproduct函數中的多參數寫法和單參數寫法，琢磨琢磨逗号和乘号的問題。

多條件計數的和其實又有兩種情況，就是“和”在哪個條件上體現。

例如：

問：周婉悅和玉玲師太成功賣出玉香劍的天數？

問：周婉悅成功賣出玉香劍和西風刀的天數？

二者是不一樣的，但都屬于多條件計數的和。

我們來解決：周婉悅和玉玲師太成功賣出玉香劍的天數。

（1）方法一：篩選法

篩選銷售人員：周婉悅、玉玲師太；篩選文創産品：玉香劍，答案為3

（2）方法二：COUNTIFS

周婉悅賣出玉香劍的天數 玉玲師太賣出玉香劍的天數

=COUNTIFS(D2:D18,D2,E2:E18,E2) COUNTIFS(D2:D18,D18,E2:E18,E2)

（3）方法三：SUM函數

分寫累加：

周婉悅賣出玉香劍的天數 玉玲師太賣出玉香劍的天數：

=SUM((D2:D18=D2)*(E2:E18=E2)) SUM((D2:D18=D18)*(E2:E18=E2))

按Ctrl Shift Enter計算

或者換種思路，考慮求滿足周婉悅或玉玲師太，賣出玉香劍的天數：

=SUM(((D2:D18=D2) ((D2:D18)=D18))*(E2:E18=E2))

合寫：

=SUM((D2:D18={"周婉悅","玉玲師太"})*(E2:E18=E2))

按Ctrl Shift Enter計算

異向一維數組邏輯運算構建二維數組，二維數組再與一維數組相乘，轉換為數值數組再求和輸出。

合寫再進一步，虛拟構建：

=SUM((D2:D18=IF({1,0},D2,D18))*(E2:E18=E2)) 按Ctrl Shift Enter計算

=SUM((D2:D18=CHOOSE({1,2},D2,D18))*(E2:E18=E2)) 按Ctrl Shift Enter計算

（4）方法四：SUMPRODUCT函數

分寫累加：

周婉悅賣出玉香劍的天數 玉玲師太賣出玉香劍的天數。

多參數分寫：

=SUMPRODUCT((D2:D18=D2)*1,(E2:E18=E2)*1) SUMPRODUCT((D2:D18=D18)*1,(E2:E18=E2)*1)

單參數分寫：

=SUMPRODUCT((D2:D18=D2)*(E2:E18=E2)) SUMPRODUCT((D2:D18=D18)*(E2:E18=E2))

按Enter計算

或者換種思路，考慮求滿足周婉悅或玉玲師太，賣出玉香劍的天數：

=SUMPRODUCT(((D2:D18=D2) (D2:D18=D18)),((E2:E18=E2))*1)

=SUMPRODUCT(((D2:D18=D2) (D2:D18=D18))*(E2:E18=E2))

合寫：

單參數合寫，或構建一下，可以用以下公式：

=SUMPRODUCT((D2:D18={"周婉悅","玉玲師太"})*(E2:E18=E2))

=SUMPRODUCT((D2:D18=IF({1,0},D2,D18))*(E2:E18=E2)) 構建數組高級用法

=SUMPRODUCT((D2:D18=CHOOSE({1,2},D2,D18))*(E2:E18=E2)) 構建數組高級用法

最後，有人就要問了，那SUMPRODUCT的多參數合寫行不行呢？

答案是不行！

=SUMPRODUCT((D2:D18={"周婉悅","玉玲師太"})*1,(E2:E18=E2)*1) 報錯#VALUE!

=SUMPRODUCT((D2:D18=IF({1,0},D2,D18))*1,(E2:E18=E2)) 報錯#VALUE!

=SUMPRODUCT((D2:D18=CHOOSE({1,2},D2,D18))*1,(E2:E18=E2)) 報錯#VALUE!

為什麼！？

如果你真的基礎紮實，這裡出錯的原因應該能想出來：

因為sumproduct要求參與計算的數組必須具有相同的維度；

(D2:D18={"周婉悅","玉玲師太"})*1的結果會是二維數組；

(E2:E18=E2)*1的結果會是一維數組；

故SUMPRODUCT函數報錯#VALUE!

最後，多條件計數的和的另一個問題，問：周婉悅成功賣出玉香劍和西風刀的天數，應該就不是問題了！

你學會了？還是學廢了？

好了，以上就是連載7的全部内容，相信你對SUMPRODUCT函數、數組有了更深入的認識，如果有不理解的可以先看前面的連載打牢基礎。

點擊頭像或鍊接跳轉：

穿越時間•Excel升級之路連載1：Office2010安裝體驗及個性設置

穿越時間•Excel升級之路連載2：打牢Excel 2010中的數據類型基礎

穿越時間•Excel升級之路連載3：數據類型檢測與相互轉換技巧

穿越時間•Excel升級之路連載4：精通公式運算規則和字符比較

穿越時間•Excel升級之路連載5：數組的理想照進現實

穿越時間·Excel升級之路連載6：神級函數SUMPRODUCT的驚鴻一瞥

更多精彩，敬請關注，投币贊賞，感謝支持。

（原創連載，個人觀點保留，禁止任何未經授權的非本人賬号複制文章到其他平台發布）

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