COUNTIF具有多个条件和列表 [英] COUNTIF with multiple criteria and list

问题描述

我需要计算CRITERIA 1满足的单元格数量，CRITERIA 2被满足，CRITERIA 3属于E列中所包含的值。

我目前使用以下公式：

  = SUM（COUNTIFS（A2：A11，TRUE ，B2：B11，TRUE，C2：C11，{2，4，6，9，10}））
  pre> 
 
 
但在我的真实表格中，标准3中的数据列表更长更复杂，我宁愿引用列E中的单元格，而不是具体数据，例如：
  = SUM（COUNTIFS（A2：A11，TRUE，B2：B11，TRUE ，C2：C11，{E2：E6}））
  
请注意，在这个例子中与我的真实表中的数据不同。真正的表比这张表要长得多，复杂得多。
 
 
 
任何建议？
 
 
 
  
解决方案
决定把我的评论作为答案，所以我可以显示一个它的作品： p> 
 
 
你很近该范围是一个数组，所以不需要 {} 包装器
 
 
 
只需使用：
  = SUM（COUNTIFS（A2：A11，TRUE，B2：B11，TRUE，C2：C11，E2：E6） 
  
这是一个数组公式，必须用Ctrl-Shift-Enter确认。
 
 
 
  
 
I need to count the number of cells in which 'CRITERIA 1' is satisfied, 'CRITERIA 2' is satisfied and 'CRITERIA 3' falls within the set of values contained in column E.

I am currently using the following formula:
=SUM(COUNTIFS(A2:A11,"TRUE",B2:B11,"TRUE",C2:C11,{"2","4","6","9","10"}))
But in my real table, the list of data within 'CRITERIA 3' is longer and more complicated and I would prefer to reference the cells in column E rather than the specific data, i.e. something like:
=SUM(COUNTIFS(A2:A11,"TRUE",B2:B11,"TRUE",C2:C11,{"E2:E6"}))
Please note that the data contained in this example is different to the data in my real table. The real table is considerably longer and more complex than this table.

Any suggestions?

解决方案
Decided to put my comment as an answer so I can show a picture that it works:

You are close.  The Range is an array so no need for the {""} wrapper

Just use:
=SUM(COUNTIFS(A2:A11,"TRUE",B2:B11,"TRUE",C2:C11,E2:E6))
This is an array formula and must be confirmed with Ctrl-Shift-Enter.



                        
这篇关于COUNTIF具有多个条件和列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！