vba COUNTIFS 包括整列而不是单个项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27027641/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COUNTIFS including entire column rather than one single item
提问by Kelsie B
Goal: To run a COUNTIFS matching Sheet2!A:A to Sheet1!B:B and a completed status from sample below. The goal is for sheet 1 to have training information pulled from a database and then I need to calculate who from that database has completed a list of specific training items (listed on sheet 2).
目标：运行 COUNTIFS 匹配 Sheet2!A:A 到 Sheet1!B:B 以及以下示例中的已完成状态。目标是让工作表 1 从数据库中提取培训信息，然后我需要计算该数据库中谁完成了特定培训项目的列表（列在工作表 2 中）。
I have tried to set up a COUNTIFS but can only do it for each individual training item. I would have to set up a long string of these adding up and also include a user condition for the formula to work properly.
我曾尝试设置 COUNTIFS，但只能针对每个单独的培训项目进行设置。我必须设置一长串这些加起来，还包括一个用户条件，以使公式正常工作。
COUNTIFS(Sheet1!B:B,**Sheet2!A2**,Sheet1!C:C,"complete")
COUNTIFS(Sheet1!B:B,**Sheet2!A2**,Sheet1!C:C,"complete")
To save me a lot of time and potential errors, I would like to to run:
为了节省我大量的时间和潜在的错误，我想运行：
COUNTIFS(Sheet1!B:B,**Sheet2!A:A**,Sheet1!C:C,"complete")
COUNTIFS(Sheet1!B:B,**Sheet2!A:A**,Sheet1!C:C,"complete")
However, when I enter the formula it displays an error. I also have tried to utilize pivot tables but it will not allow me to complete a COUNTIFS as a calculated field for the table. I am open to using VBA, but have limited experience in it if that will solve my issue.
但是，当我输入公式时，它显示错误。我也曾尝试使用数据透视表，但它不允许我完成 COUNTIFS 作为表的计算字段。我愿意使用 VBA，但如果这能解决我的问题，我的经验有限。
Sheet 1:
第 1 页：
Name LO ID/Title Status
user 1 LO 1 complete
user 1 LO 2 complete
user 1 LO 3 registered
user 2 LO 1 complete
user 2 LO 3 complete
user 2 LO 4 complete
Sheet 2:
第 2 页：
Cirricula
LO 1
LO 3
LO 4
EDIT: Ideal output on Sheet 2:
编辑：工作表 2 上的理想输出：
User 1 - 1(because they only have LO1 complete, LO2 is not required in the cirricula, and LO3 is a registered status)
用户 1 - 1（因为他们只有 LO1 完成，循环中不需要 LO2，而 LO3 是注册状态）
User 2 - 3(because they have all three items complete required in the cirricula)
用户 2 - 3（因为他们完成了循环中要求的所有三个项目）
采纳答案by pnuts
This involves manual selection (to exclude LO 2) of the LO ID/Titlefilter (unless set with VBA for example) but seems to give the results required:
这涉及手动选择（排除LO 2）LO ID/Title过滤器（除非使用 VBA 设置），但似乎给出了所需的结果：
回答by tospig
On Sheet 2, column B you can use
在工作表 2 上，您可以使用 B 列
=SUMPRODUCT((Sheet1!$B:$B=A2)*(Sheet1!$C:$C="complete"))
which will give as output
这将作为输出
Cirricula Count complete
LO 1 2
LO 3 1
LO 4 1
Update / Edit
更新/编辑
To reference the user, just reference Column A in stead of Column B (or whereever your usercolumn is):
要引用user，只需引用 A 列而不是 B 列（或您的user列所在的任何位置）：
=SUMPRODUCT((Sheet1!$A:$A=D2)*(Sheet1!$C:$C="complete"))
Here on Sheet 2 I have Userin column D
在第 2 页上，我User在列中D
Cirricular count cirricular complete User counter user complete
LO 1 2 user 1 2
LO 2 1 user 2 3
LO 4 1
Update / Edit 2
更新/编辑 2
To get what you want using formula, I have included a vlookupin Column C to see if the LO IDis in the required list of LO IDs. You can then add this third criteria in the sumproductformula.
为了使用公式获得您想要的结果，我vlookup在 C 列中包含了 a以查看sLO ID是否在所需的LO IDs列表中。然后，您可以在sumproduct公式中添加这第三个条件。
(The vlookupin Column C is =IF(ISERROR(VLOOKUP(B2,$F$2:$F$4,1,FALSE)),"no","yes"))
（vlookupC 列中的为=IF(ISERROR(VLOOKUP(B2,$F$2:$F$4,1,FALSE)),"no","yes")）
回答by L42
Suppose you have your data like this:
假设你有这样的数据：
Note that the LO List is in Sheet2. Enter this formula in F2:
请注意，LO 列表位于 Sheet2 中。在 F2 中输入此公式：
=SUMPRODUCT(COUNTIFS(A$2:A$7,E2,B$2:B$7,Sheet2!A$2:A$4,C$2:C$7,"complete"))
=SUMPRODUCT(COUNTIFS(A$2:A$7,E2,B$2:B$7,Sheet2!A$2:A$4,C$2:C$7,"complete"))
Above formula will give you the result you described above.
Not far from what I posted earlier, we just added another criteria range and criteria.
In short, you only need to incorporate SUMPRODUCT
in your COUNTIFSformula when dealing with multiple criteria.
上面的公式会给你你上面描述的结果。
与我之前发布的内容相距不远，我们刚刚添加了另一个标准范围和标准。
简而言之，在处理多个条件时，您只需要在COUNTIFS公式中加入SUMPRODUCT。
Note:Your formula above is just lacking SUMPRODUCTand the other criteria for the useralthough I don't recommend using the entire column as criteria since it will hurt Excel's calculation time. Your best bet if your data is dynamic is to use a Dynamic Named Rangefor the multiple criteria. HTH.
注意：您上面的公式只是缺少SUMPRODUCT和用户的其他标准，尽管我不建议使用整列作为标准，因为它会影响 Excel 的计算时间。如果您的数据是动态的，最好的选择是对多个条件使用动态命名范围。哈。
