(乾貨)COUNTIF、COUNTIFS的區別及使用條件

之前講過SUMIF、SUMIFS、SUMPRODUCT的區別和使用條件,今天我們講一下這兩個函數。

我們還拿之前的數據舉例,這次只有姓名和月份,沒有銷售額,換了幾個條件,同時也把SUMPRODUCT函數拿來做對比。

誒,講到這裡有同學肯定會問為什麼不把SUM、SUMIF、SUMIFS一起拿來做對比呢?

這裡就是這兩類函數的本質區別,SUM相關函數是求和,且求和參數必須是數值;而COUNT相關函數是計數,對參數是什麼類型的沒有限制。

那又有同學問,為什麼SUMPRODUCT函數可以拿來做參考呢,是因為SUMPRODUCT本身是先乘積再求和,如果參數中的結果都是0或1,那求和的值是不是等於計數的值?我們來看一下就知道了。

條件1

A的數量

只有一個條件,三個都支持

在D2輸入 =COUNTIF(A2:A17,"A"),結果為 4

在E2輸入 =COUNTIFS(A2:A17,"A"),結果為 4

在F2輸入 =SUMPRODUCT(--(A2:A17="A")),結果為 4

看到沒有,F2結果也是4,但在公式中有一個 -- ,這是什麼鬼呢?那這個你可以理解成負負得正的意思,可以把非數值型的數組元素轉化成數值,參與運算。若不加 -- ,函數會把這些元素作為0處理。

不懂沒關係,我們在編輯欄用游標選中 A2:A17="A",按F9看下結果,

接著我們選中 --(A2:A17="A"),按F9看下結果,

看到了吧,加上 -- ,使TRUE -> 1,FALSE -> 0,接著求和計算。

把 -- 替換成 *1,也是一樣的,任何數*1還是這個數,但乘以1後就變成了可計算的數值型元素。

條件2

A和B的數量

這個意思就是統計姓名為A的,統計姓名為B的最後相加,所以我們需要用SUM求和。

在D3輸入 =SUM(COUNTIF(A2:A17,{"A","B"})),結果為8

在E3輸入 =SUM(COUNTIFS(A2:A17,{"A","B"})),結果為 8

在F3輸入 =SUMPRODUCT(--(A2:A17={"A","B"})),結果為 8

條件3

A和B 1月的數量

這種情況下COUNTIF就比支持了,兩個參數下的條件。

在E4輸入 =SUM(COUNTIFS(A2:A17,{"A","B"},B2:B17,1)),結果為 2

在F4輸入 =SUMPRODUCT(--(A2:A17={"A","B"})*(B2:B17=1)),結果為 2

條件4

A和B,1月和3月的數量

COUNTIF不支持,SUMPRODUCT需要分開求和

在E5輸入 =SUM(COUNTIFS(A2:A17,{"A","B"},B2:B17,{1;3})),結果為 5

在F5輸入 =SUMPRODUCT(((A2:A17="A") (A2:A17="B"))*((B2:B17=1) (B2:B17=3))),結果為 5

條件5

A 1月和C 3月的數量

COUNTIF不支持,COUNTIFS是找出A 1月的,再找出C 3月的,最後用SUM相加

在E6輸入 =SUM(COUNTIFS(A2:A17,{"A","C"},B2:B17,{1,3})),結果為 2

在F6輸入 =SUMPRODUCT((A2:A17={"A","C"})*(B2:B17={1,3})),結果為 2

講到這裡,需要給大家提一下為什麼後面COUNTIFS計算中要用SUM求和,那條件4為例,這是求A1月數量、A3月數量、B1月數量、B3月數量,那放到表格里是不是應該這樣顯示:

有木有很像數組的感覺,紅色區域是求值的地方,如果不加SUM,我們在第一個紅格寫入公式=COUNTIFS(A2:A17,{"A","B"},B2:B17,{1;3}),結果為 1,為什麼是1,實際上這裡求的是A1月的個數。那如果我們選中全部紅色區域,再輸入上面這個公式,並三鍵,看下結果:

接著在公式外面套一個SUM函數,意味著把紅色區域中的結果相加,得到 5。這樣說你明白了嗎:)


歡迎關注微信公眾號:教小白EXCEL(XiaoBai_EXCEL)

小白,我在等你呦!

本文為頭條號作者發布,不代表今日頭條立場。

推薦閱讀:

土雞蛋與飼料蛋、人造蛋的區別
傳統太極拳與競技太極拳的區別
最高院民一庭:重婚與「有配偶者與他人同居」的區別
未婚的女人和離婚的女人會有什麼區別?

TAG:乾貨 | 條件 | 區別 |