如何在Excel中对多个条件进行计数？
在Excel中，COUNTIF函数可以帮助我们计算列表中某个值的数量。 但是有时，我们需要使用多个条件进行计数，这将变得更加复杂，今天，我将讨论一些使用多个条件进行计数的项目。
Countif在同一列中具有多个条件
根据文本值对具有多个条件的单元格进行计数
例如，我有以下包含一些产品的数据，现在我需要计算同一列中填充的KTE和KTO的数量，请参见屏幕截图：
要获取KTE和KTO的数量，请输入以下公式：
然后按 输入 获取这两个产品编号的关键。 看截图：
备注：
1.在上式中： A2：A15 是您要使用的数据范围， 韩国电信 和 韩国旅游发展局 是您要计算的标准。
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之间的数字的结果。请使用以下公式：
将此公式输入到要查找结果的空白单元格中：
然后按 输入 获得所需结果的关键，请参见屏幕截图：
备注：在上式中：
 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之间的单元格编号，请这样做：
在空白单元格中输入以下公式：
然后按 输入 获取计数的键，请参见屏幕截图：
备注：在上式中：
 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数量。
请将此公式键入所需的单元格：
然后按 输入 密钥以获取所需的KTE数量。
备注：
1. A2：A15 和 韩国电信 是您需要的第一个范围和标准， 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 20072019和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.