如何在Excel中對多個條件進行計數?
在Excel中,COUNTIF函數可以幫助我們計算列表中某個值的數量。 但是有時,我們需要使用多個條件進行計數,這將變得更加複雜,今天,我將討論一些使用多個條件進行計數的項目。

Countif在同一列中具有多個條件
根據文本值對具有多個條件的單元格進行計數
例如,我有以下包含一些產品的數據,現在我需要計算同一列中填充的KTE和KTO的數量,請參見屏幕截圖:
要獲取KTE和KTO的數量,請輸入以下公式:
然後按 Enter 獲取這兩個產品編號的關鍵。 看截圖:
筆記:
1.在上式中: A2:A15 是您要使用的數據范圍, KTE 或 韓國旅遊發展局 是您要計算的標準。
2.如果您要在一列中計算兩個以上的條件,只需使用= COUNTIF(range1,條件1)+ COUNTIF(範圍2,條件2)+ COUNTIF(範圍3,條件3)+…
- 提示:
- 另一個簡潔的公式也可以幫助您解決此問題: =SUMPRODUCT(COUNTIF($A$2:$A$15,{"KTE";"KTO"})), and then press Enter key to get the result.
- 您可以像添加標準一樣 =SUMPRODUCT(COUNTIF(range,{ "criteria1";"criteria2";"criteria3";"criteria4"…})).
計算兩個值之間具有多個條件的單元格
如果需要計算該值在兩個給定數字之間的單元格數,那麼如何在Excel中解決此工作?
以下面的屏幕截圖為例,我想得到介於200和500之間的數字的結果。請使用以下公式:
將此公式輸入到要查找結果的空白單元格中:
然後按 Enter 獲得所需結果的關鍵,請參見屏幕截圖:
備註:在以上公式中:
- B2:B15 是您要使用的單元格範圍, > 200 或 > 500 您要對細胞進行計數的標準;
- 整個公式意味著找到值大於200的單元格數量,然後減去值大於500的單元格數量。
- 提示:
- 您也可以應用COUNTIFS函數來處理此任務,請輸入以下公式: =COUNTIFS($B$2:$B$15,">200",$B$2:$B$15,"<500"), and then press Enter key to get the result.
- 您可以像添加標準一樣 =COUNTIFS(range1,"criteria1",range2,"criteria2",range3,"criteria3",...).
在兩個日期之間對具有多個條件的單元格進行計數
要基於日期範圍對單元格進行計數,COUNTIF和COUNTIFS函數也可以幫您一個忙。
例如,我要在一欄中計算日期在5/1/2019和8/1/2019之間的單元格編號,請這樣做:
在空白單元格中輸入以下公式:
然後按 Enter 獲取計數的鍵,請參見屏幕截圖:
備註:在以上公式中:
- B2:B15 是您要使用的單元格範圍;
- > = 5/1/2018 或 <= 8/1/2019 您要計算單元格的日期標準;
Countif在同一列中具有多個條件並具有有用的功能
如果你有 Kutools for Excel,其 選擇特定的單元格 功能,您可以快速選擇具有特定文本的單元格或兩個數字或日期之間的單元格,然後獲取所需的數字。
提示:要應用此 選擇特定的單元格 功能,首先,您應該下載 Kutools for Excel,然後快速輕鬆地應用該功能。
安裝後 Kutools for Excel,請這樣做:
1。 選擇要根據條件對單元格進行計數的單元格列表,然後單擊“確定”。 Kutools > 選擇 > 選擇特定的單元格,請參見屏幕截圖:
2。 在 選擇特定的單元格 對話框,請根據需要設置操作,然後單擊 OK,已經選擇了特定的單元格,並且在提示框中顯示了單元格的數量,如下面的屏幕截圖所示:
![]() |
![]() |
![]() |
備註:此功能還可以幫助您選擇和計算兩個特定數字或日期之間的單元格,如以下屏幕截圖所示:
![]() |
![]() |
![]() |
Countif在多個列中具有多個條件
如果在多列中有多個條件,如下面的屏幕截圖所示,我想獲取階數大於300且名稱為Ruby的KTE數量。
請在所需的單元格中鍵入以下公式:
然後按 Enter 密鑰以獲取所需的KTE數量。
筆記:
1. A2:A15 或 KTE 是您需要的第一個範圍和標準, B2:B15 或 > 300 是您需要的第二個範圍和標準,並且 C2:C15 或 紅寶石 是您基於的第三個範圍和條件。
2.如果需要更多標準,則只需在公式中添加範圍和標準,例如:= COUNTIFS(range1,criteria1,range2,criteria2,range3,criteria3,range4,criteria4,…)
- 提示:
- 這是另一個公式也可以幫助您: =SUMPRODUCT(--($A$2:$A$15="KTE"),--($B$2:$B$15>300),--($C$2:$C$15="Ruby")), and then press Enter key to get the result.
更多相對計數單元文章:
- Countif在Excel中計算百分比
- 例如,我有一份研究論文的摘要報告,有三個選項A,B,C,現在我要計算這三個選項中每個選項的百分比。 也就是說,我需要知道選項A佔所有選項的百分比。
- 跨多個工作表的特定值
- 假設我有多個包含以下數據的工作表,現在,我想從這些工作表中獲取特定值“ Excel”的出現次數。 如何計算多個工作表中的特定值?
- Countif Excel中的部分字符串/子字符串匹配
- 統計包含某些字符串的單元格很容易,但是您知道如何在Excel中統計僅包含部分字符串或子字符串的單元格嗎? 本文將介紹幾種快速解決問題的方法。
- 計算Excel中除特定值以外的所有單元格
- 現在,如果將“ Apple”一詞散佈在值列表中,則只想計算非“ Apple”的單元格數即可得到以下結果。 在本文中,我將介紹一些解決Excel中任務的方法。
- 如果在Excel中滿足多個條件之一,則對單元格進行計數
- COUNTIF函數將幫助我們對包含一個條件的單元格進行計數,而COUNTIFS函數可以幫助對Excel中包含一組條件或條件的單元格進行計數。 如果計數單元格包含多個條件之一怎麼辦? 在這裡,我將分享計算Excel中包含X或Y或Z…等的單元格的方法。
最佳辦公效率工具
Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%
- 重用: 快速插入 複雜的公式,圖表 以及您以前使用過的任何東西; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
- 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
- 合併單元格/行/列 不會丟失數據; 拆分單元格內容; 合併重複的行/列...防止細胞重複; 比較範圍...
- 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
- 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
- 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
- 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
- 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
- 超過300種強大功能。 支持Office / Excel 2007-2019和365。支持所有語言。 在您的企業或組織中輕鬆部署。 完整功能30天免費試用。 60天退款保證。

Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆
- 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 每天將您的工作效率提高50%,並減少數百次鼠標單擊!

You are guest
or post as a guest, but your post won't be published automatically.
-
To post as a guest, your comment is unpublished.· 9 months agoHi,I need to count the different names in a single column which is repeated more than one time..aaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbbbbbbbbbcccccccccccccccccccci dont want to mention names in formula, because its many names in the column and i dont want pivot table also,I tried with this formula, but its counting the blank also..=SUMPRODUCT(1/COUNTIF(C10:C500,C10:C500&""))Result what i need is -aaaa - 4bbbb - 6cccc - 5
-
To post as a guest, your comment is unpublished.Hi, I am wondering if someone can help me figure our how to combine COUNTIFS formulas. For example, I need to present this more efficiently:
=COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1",VAR4,PH,VAR5,"Pres") +
COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1",VAR4,"Both", VAR5,"Pres") +
COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1", VAR6,PH, VAR5,"Pres") +
COUNTIFS(VAR1,”Yes”,VAR2,"2016",VAR3,"Q1", VAR6,"Both", VAR5,"Pres")
So I need counts if VAR1 =Yes; VAR2 = 2016; VAR3=Q1; VAR5=Pres; AND VAR4= PH or Both; AND VAR6=PH or Both. I think the way I have it does the job, but I'm sure there is a more concise way to write the formula as it may get very long as I add criteria to it. -
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.hi, need to countif from two file with range and selection
=IF((COUNTIFS([SCCD.xlsx]open!$AV:$AV,">6",$AV:$AV,"<10"),[SCCD.xlsx]open!$T:$T,C2,[SCCD.xlsx]open!$V:$V,"SLAHOLD")+COUNTIFS([KTPH.xlsx]open!$H:$H,"SLAHOLD",[KTPH.xlsx]open!$J:$J,">6",!$J:$J,"<10",[KTPH.xlsx]open!$G:$G,C2))=0,"",COUNTIFS([SCCD.xlsx]open!$AV:$AV,">6",$AV:$AV,"<10"),[SCCD.xlsx]open!$T:$T,C2,[SCCD.xlsx]open!$V:$V,"SLAHOLD")+COUNTIFS([KTPH.xlsx]open!$H:$H,"SLAHOLD",[KTPH.xlsx]open!$J:$J,">6",$J:$J,"<10",[KTPH.xlsx]open!$G:$G,C2))
got error.. can someone advice -
To post as a guest, your comment is unpublished.Hi. I need to select information of cells range h6 to m126. I then need to count how many of these are male (and female) from cells range c6 to c126. I have tried =countifs($h$6:$m$126,”B1”,$c$6:$c6$C126,”M”) but when I enter it is coming up as #value!
Any advice will be gratefully received.
Thanks. -
To post as a guest, your comment is unpublished.Hi, I have trouble making a formula to this. kindly help me if there are existing formula for this.. thanks! pls see below.
There are diff. zone in a column.. each row has open or closed remarks. How can I add all the open and closed items for each zone?
Column: ZONE No. Remarks
1 open
2 open
1 open
1 close
This is the sample data.. I wanted to know how many are still open/closed per zone number.-
To post as a guest, your comment is unpublished.Hi, Marizze,
Maybe the below formulas can solve your problem:
All open item with zone number 1: =COUNTIFS(B2:B8, "open",A2:A8,"1");
All close item with zone number 1: =COUNTIFS(B2:B8, "close",A2:A8,"1")
with the same formulas to get other zone number result as you need.
Please try, hope it can help you!
-
-
To post as a guest, your comment is unpublished.Ok So I have a complicated one
I need to pull data to a table to show :
The total number of overdue items based on the date now for a specific centre
So the total number of overdue items in the grace centre where the data table contains multiple centres
the formula I use for the overdue items is =countif(rawdata!I:I,''<''&D12) - where D12 formula contains =NOW()-0
This brings back the overdue items based on date for all centres but I want it specially for those which are only overdue for the grace centre and the centre data is in column E.
I have tried adding ,rawdata!E:E,''Grace''), but it comes back too many arguments
Can I not use multiple formula for the -
To post as a guest, your comment is unpublished.I HAVE A TABLE OF STUDENTS WITH GENDER IN A COLUMN AND RACE IN ANOTHER COLUMN. HOW CAN I FIND THE NUMBER OF A SINGLE RACE BY MALE OR FEMALE DIFFERENTLY?
-
To post as a guest, your comment is unpublished.Hello, Rajan,
To solve your problem, you should apply the below formulas:
Count the number of Male: =COUNTIF($B$2:$B$12,"Male");
Count the number of Female: =COUNTIF($B$2:$B$12,"Female")
Please try, hope it can help you!
-
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.hi. hope i can get help with the setting up the correct data table and how to extract specific information from the table. here are the variables:
we have multiple products under several different categories
we have multiple sales rep assigned to specific territories
i need to track their individual sales per product
i also need to break down their sales per month, quarter, and on an annual basis (still per category, product and area)
i need to compare the data of their actual sales versus their targets
what's the correct data set, and the correct formula for it? thanks
with these, i can then make a pivot table out of the data table. -
To post as a guest, your comment is unpublished.Can multiple arrays are possible 8n single countifs?
Countifs(range,{criteria: criteria},range,{criteria: criteria}, range,{criteria: criteria}) -
To post as a guest, your comment is unpublished.My column A contains a set of different categories. My column B contains dates as "1 October 2018", but my filter is by year ("2018").
Both [ =SUMPRODUCT(--(A:A="Category x"),--(B:B="2018") ] and [ =COUNTIFS(A:A,"Category x",B:B,"2018) ] give me a result of zero, which is evidently incorrect. Could there by something wrong with my date filter?
Thanks! -
To post as a guest, your comment is unpublished.I am not able to upload the image of my data. neither .png file nor .bmp file upload. Any advice anyone?
thanks
Dave U. -
To post as a guest, your comment is unpublished.I'm showing 3 tables. The middle table shows lab data. In my example, I want to count any platelet values (PLAT) that have a supporting event in the left table, with matching dates. My formula in Column M looks like this:
SUM(COUNTIFS(C:C, J13, D:D, {"Thrombocytopenia","Platelet count decreased"}, E:E, "<="&EDATE(L13, 0), F:F, ">="&EDATE(L13, 0)) + COUNTIFS(C:C, J13, D:D, {"Thrombocytopenia","Platelet count decreased"}, E:E, "<="&EDATE(L13, 0), G:G, "AFTER"))
This formula works; however, I must HARDCODE the values "Thrombocytopenia" and "Platelet count decreased". I would like it to work dynamically where it references Column Q, or perhaps cells Q10 and Q11, where it uses that text based on the matching lab name (e.g., PLAT). In essence, I'm looking for a nested OR statement that behaves dynamically within the middle of a COUNTIFS statement. Tricky..... maybe I need to learn how to use --SUMPRODUCT. Notice the NEUT lab test in the far right table which has 3 "events" that would be acceptable to find in the leftmost table... I would want them to be counted, eventually when I find a good formula.
thanks - Dave U -
To post as a guest, your comment is unpublished.I have another request if possible, I am looking for a formula that will give me staff holiday cover, there are 5 people and each person has to cover at least one day over Christmas and New Year, each person has to give me what holiday entitlement they have left for the year so that I can calculate the cover.
-
To post as a guest, your comment is unpublished.Just wondering if you can help, I need a formula to decide a Pass or Fail as the result. The following data is in 6 columns with either a yes or no in them, if the results are all “yes” then this is a pass, if any one column has a “no” then this is a fail. I have tried various formulas with “IF” “AND” “OR” but nothing gives me what I am looking for. Thank you in advance.
-
To post as a guest, your comment is unpublished.I have a work Count the students branch wise and course wise i have Ex. A1 course like B.tech or Diploma A2 Have Branch EEE,ECE and soon i want count diploma all banchs and btech all banchs any formula for that
sheet enclosed -
To post as a guest, your comment is unpublished.please assist. I want to count the number of blank columns next to a certain name.
I am trying to use "=countifs", but struggling with the blank part...
for example:
column A Column B
Lesley Nico
Lesley Sipho
Lesley
Lesley Floyd
Bronz Sam
Bronz Gift
Bronz
Bronz
Result should be:
Lesley 1
Bronz 2 -
To post as a guest, your comment is unpublished.Okay I'm soooo stuck with this formula. Here's what I have
= SUMPRODUCT(--(F2:F77=FALSE),--(G2:G77=FALSE))
Now I also have a column H. I need the formula to count if G and H are false but if I do
= SUMPRODUCT(--(F2:F77=FALSE),--(G2:H77=FALSE))
or
= SUMPRODUCT(--(F2:F77=FALSE),--(G2:G77=FALSE)--(H2:H77=FALSE))
it won't allow either. Please help!
Thanks-
To post as a guest, your comment is unpublished.
-
-
To post as a guest, your comment is unpublished.Hi,
I have a large document of data in the below format:
Offer Start date End date
Offer 1 12/08/2018 18/08/2018
Offer 2 13/08/2018 26/08/2018
Offer 3 13/08/2018 26/08/2018
Offer 4 14/08/2018 01/09/2018
Offer 5 20/08/2018 26/08/2018
Offer 6 27/08/2018 08/09/2018
Offer 7 09/08/2018 12/08/2018
Offer 8 08/08/2018 18/08/2018
I need to calculate a number of offers avaliable each week. The final document should be in the format below:
WeekNum Start date End date Offer count
31 30/07/2018 05/08/2018
32 06/08/2018 12/08/2018
33 13/08/2018 19/08/2018
34 20/08/2018 26/08/2018
35 27/08/2018 02/09/2018
36 03/09/2018 09/09/2018
37 10/09/2018 16/09/2018
In theory, it's relatively easy. You can use COUNTIFS to calculate cells when the offer end date is between the week start date and week end date. The problem however is when offer lasts for more than 1 week. Eg. Offer 8 lasts until December 31 which means it needs to be counted as one every week from week 32 to week 53. Do you have any ideas how this could be calculated?
Thanks! -
To post as a guest, your comment is unpublished.Hello,
Good day ...
We have two results from an item number from different location
that will show like
Eg.
C1 C2 C3
item#123 Required Not Required not Required
From this this 2 answers the final answer will be 'required' if required available on column
If 'required' not available then answer will be 'Not Required'
In final cell I would like to get one answer
-
To post as a guest, your comment is unpublished.I have a column with Multiple names and i wanted to find the count of the names except a perticular name. Can some body help me??
Column Values: a b a b c d e f a b a x y z (Here i want count of a & b & c) without using countif(A:A,"a")+countif(A:A,"b")+countif(A:A,"c").-
To post as a guest, your comment is unpublished.
-
-
To post as a guest, your comment is unpublished.lets say I have these values, 1 to 1.5 will be a 1, 1.6 to 3 will be a 2, 3.1 to 4.5 will be a 3 and 4.6 to 6 will be 4. How do I put that formula for several values, like lets say I have a list with 100 items and their values vary between 1 and 6. So every time I log in a number it will automatically give me the value. Thank you.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.=IF(Working!C3=Working!B7,SUM(COUNTIFS(Gender,"Male",Category,{"Bombay","Pune"},Class,{"1","2","3","4"})))
Am not getting the correct answer for this, getting output for only 1st criteria.
*(Working - Sheet Name)
Kindly help-
To post as a guest, your comment is unpublished.Hello, Waseem,
Can you give an example of your problem?
You can attach a screenshot here!
Thank you!-
To post as a guest, your comment is unpublished.I dono its not uploading image, trying again,,
-
To post as a guest, your comment is unpublished.Thanks for ua consideration... Below is the formula again..
If B14 matches with A17, then I want the number of counts of 'Male' from Bombay and Pune and they should be in class 1 to class 4.. (For this answer should be 3, but am not getting that)
=IF(B14=A17,SUM(COUNTIFS(Gender,"Male",Category,{"Bombay","Pune"},Class,{"1","2","3","4"})))-
To post as a guest, your comment is unpublished.Here is the screenshot..
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
-
-
-
-
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.i'm trying to count the number of cells where the date is within a certain range, no problems, i have the formula =SUMPRODUCT((P8:P253>=DATEVALUE("1/7/2017"))*(P8:P253<=DATEVALUE("31/07/2017"))) also trying to count where the product of another cell is another condition, say A1, again no problems, i have =COUNTIF(E8:E253,"A1") but how do i combine the two as conditional where i get the number of cells between a certain date range that contain a specific entry? thanks
-
To post as a guest, your comment is unpublished.
-
-
To post as a guest, your comment is unpublished.i'm trying to count the number of cells where the date is within a certain range, no problems, i have the formula =SUMPRODUCT((P8:P253>=DATEVALUE("1/7/2017"))*(P8:P253<=DATEVALUE("31/07/2017"))) also trying to count where the product of another cell is another condition, say A1, again no problems, i have =COUNTIF(E8:E253,"A1") but how do i combine the two as conditional where i get the number of cells between a certain date range that contain a specific entry? thanks
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.how to count a , b , c , d in excel . i want to count only a b d in excel not c .
please tell formula-
To post as a guest, your comment is unpublished.A
B
C
D
=COUNTIF(B2:B5,"A")+COUNTIF(B2:B5,"B")+COUNTIF(B2:B5,"D")
-
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.On formula =SUMPRODUCT(COUNTIF(range,{ "criteria";"criteria";"criteria";"criteria"…})), what if i want the content from some cell to form the criterias? Like this =SUMPRODUCT(COUNTIF(A:A,{C1;C1&",*";"*,"&C1,"*,"C1&",*"})), here i got syntax error. Seems it's illegal to use cell reference in {} array.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.
-
To post as a guest, your comment is unpublished.