Excel不同列多條件計數

=SUMPRODUCT(($C$1:$C$10000="hete->s288c")*($H$1:$H$10000<900))-SUMPRODUCT(($C$1:$C$10000="hete->s288c")*($H$1:$H$10000<895))

=SUMPRODUCT(($C$1:$C$1000000<50000)*($C$1:$C$1000000>45000)*$U$1:$U$1000000)

條件的求和

複製excel中的公式，而不是公式後得到的結果

假設公式在 A列
選中A列,  按Ctrl+~     資料---分列----固定寬度---下一步------下一步      列資料格式  選擇  文字-----完成  ------ 按Ctrl+~

25	大於30且小於75的單元格個數
30
44	結果	公式
11	8	=SUM(IF(($V$4:$V$75<30)*($V$4:$V$75>0),1,0))
13	8	=COUNTIF(V4:V75,">=0")-COUNTIF(V4:V75,">=30")
-3	8	=SUMPRODUCT((V4:V75<30)*(V4:V75>0))
6	8	=SUM((V4:V75>0)*(V4:V75<30))
45
-7
20
26
35	陣列在輸入完成後要同時按Ctrl+Shift+Enter進行確定.
20
30
99
-7
19

SUMPRODUCT函式可以方便地計算工作表內多列中對應值相乘後的和，其語法為：
SUMPRODUCT(array1,array2,array3, …)
其中，Array1, array2, array3, …為 2到 30個陣列，其相應元素需要進行相乘並求和。陣列引數必須具有相同的維數，否則，函式 SUMPRODUCT將返回錯誤值 #VALUE!。該函式將非數值型的陣列元素作為 0處理。
下面通過示例介紹SUMPRODUCT函式的基本使用方法。如下圖1所示的工作表：

                                                圖1
其中所定義的名稱為：

(1)要計算工作表中姓名是張三且公司為A的用工數統計，則可以使用下面的公式：
=SUMPRODUCT(0+(姓名="張三"),0+(公司="A"),用工數)
返回結果24。
(2)要獲取姓名張三出現的次數，則可以使用下面的公式：
=SUMPRODUCT((姓名="張三")*1)
或=SUMPRODUCT(0+(姓名="張三"))
結果為5。
(3)要獲取姓名為張三且公司為A的總數，則可以使用下面的公式：
=SUMPRODUCT((姓名="張三")*(公司="A")*1)
或=SUMPRODUCT((姓名="張三")*(公司="A"))
結果為4。

                                                     圖2
(4)探討
在計算工作表中姓名是張三且公司為A的用工數統計時，使用的是公式=SUMPRODUCT(0+(姓名="張三"),0+(公司="A"),用工數)。按照常規做法，可以使用公式：
=SUMPRODUCT(姓名="張三",公司="A",用工數)
但其結果為0，即並不是所想要的正確結果24。

·        Excel在公式中能將以文字表示的數字轉換為數字，例如公式：
="3"*5
雖然”3”是文字，但該公式能返回結果15。

• Excel也能將數字轉換為文字，例如：
  ="No" & 1
  返回的結果為No1。

• Excel將邏輯值轉換為數值，例如：
  =0+TRUE返回的結果為1；
  =1*TRUE返回的結果為1。

因此，在公式中新增0強制將邏輯值進行轉換？！

 excel函式的絕對引用。

$E$2:$E$50

Excel中COUNTIF函式的使用方法彙總

 

一、求各種型別單元格的個數

(1)  求真空單元格單個數:                =COUNTIF(data,"=")

(2)  真空+假空單元格個數:   =COUNTIF(data,"")      相當於countblank()函式

(3)  非真空單元格個數:      =COUNTIF(data,"<>")     相當於counta()函式

(4)  文字型單元格個數:      =COUNTIF(data,"*")    假空單元格也是文字型單元格

(5)  區域內所有單元格個數:   =COUNTIF(data,"<>""")

(6)  邏輯值為TRUE的單元格數量 =COUNTIF(data,TRUE)

小說明：

EXCEL單元格內資料主要有以下幾類:數值型,文字型,邏輯型,錯誤值型。其中時間型別也是一種特殊的數值。文字型別的數字是文字型。

空單元格：指什麼內容也沒有的單元格，姑且稱之為真空。

假空單元格：指0字元的空文字，一般是由網上下載來的或公式得來的，姑且稱之為假空。

date指單元格區域，該引數不能是陣列

二、求><=某個值的單元格個數

(1) 大於50                           =COUNTIF(data,">50")

(2) 等於50                            =COUNTIF(data,50)

(3) 小於50                           =COUNTIF(data,"<50")

(4) 大於或等於50                     =COUNTIF(data,">=50")  

(5) 小於或等於50                     =COUNTIF(data,"<=50")&not;

(6) 大於E5單元格的值                =COUNTIF(data,">"&$E$5) 

(7) 等於E5單元格的值                 =COUNTIF(data,$E$5)  

(8) 小於E5單元格的值                =COUNTIF(data,"<"&$E$5)  

(9) 大於或等於E5單元格的值           =COUNTIF(data,">="&$E$5)

(10) 小於或等於E5單元格的值          =COUNTIF(data,"<="&$E$5)

三、等於或包含某N個特定字元的單元格個數

(1) 兩個字元                       =COUNTIF(data,"??")

(2) 兩個字元並且第2個是B         =COUNTIF(data,"?B") 

(3) 包含B                         =COUNTIF(data,"*B*")

(4) 第2個字元是B                 =COUNTIF(data,"?B*")

(5) 等於“你好”                    =COUNTIF(data,"你好")  

(6) 包含D3單元格的內容            =COUNTIF(data,"*"&D3&"*")

(7) 第2字是D3單元格的內容       =COUNTIF(data,"?"&D3&"*")

 

注：countif()函式對英文字母不區分大小寫，萬用字元只對文字有效

四、兩個條件求個數

(1)   >10並且<=15       =SUM(COUNTIF(data,">"&{10,15})*{1,-1}) 

(2)   >=10並且<15       =SUM(COUNTIF(data,">="&{10,15})*{1,-1})  

(3)   >=10並且<=15     =SUM(COUNTIF(data,{">=10",">15"})*{1,-1})

(4)   >10並且<15         =SUM(COUNTIF(data,{">10",">=15"})*{1,-1})

注：一般多條件計數使用SUMPRODUCT函式，以上方法較少使用，僅供參考。

補充：三個區域計數：

  三個區域中>=60        =SUM(COUNTIF(INDIRECT({"a46:a48","b48:b50","c47:c48"}),">=60"))

五、各種特殊統計要求的計算    A2：A32 為存放資料的區域

（1）非空文字（僅包括可鍵入文字） =COUNTIF(A2:A32,">=!") 

（2）所有非空文字

=COUNTIF(A2:A32,">=!")+COUNTIF(A2:A32,">="&CHAR(1))-COUNTIF(A2:A32,">=") 

或 {=SUM(COUNTIF(A2:A32,">="&{"!",""})*{1,-1})+COUNTIF(A2:A32,">="&CHAR(1))}

（3）全部可見單元格 

{=SUM(N(IF(ISERROR(A2:A32),1,SUBSTITUTE(A2:A32,"",""))<>""))}

（4）有效可見單元格 

=COUNTIF(A2:A32,">=!")+COUNTIF(A2:A32,">="&CHAR(1))-COUNTIF(A2:A32,">=")+COUNT(A2:A32)

（5）全部不見單元格（真空＋空格＋空文字）

=COUNTIF(A2:A32,"")-COUNTIF(A2:A32,">=!")+COUNTIF(A2:A32,">=")

（6）空格 =COUNTIF(A2:A32,">=")-COUNTIF(A2:A32,">=!") 

（7）空文字"" =COUNTIF(A2:A32,"")-COUNTIF(A2:A32,"=")

（8）邏輯與錯誤  =COUNTIF(A2:A32,"<>")-COUNTIF(A2:A32,"*")-COUNT(A2:A32)