英文:
Countifs using multiple dynamic arrays as criteria
问题
我有一个看起来让我感到困惑的COUNTIFS公式。
它使用了两个不同的条件，两者都是动态数组 - 这里由单元格引用和井号表示：
COUNTIFS(A:A, B1#, C:C, D1#)
单元格B1和D1中的动态数组运行正常，如果我更改COUNTIFS，只有一个条件引用动态范围（删除井号并选择数组中的一个结果），那么它可以正常工作。问题出在当我需要它使用两个（或更多？）动态范围的时候。
有什么想法吗？
英文:
I have a countifs formula that seems to be letting me down.
It's using 2 different criteria and both are dynamic arrays - here represented by the cell ref and the hashtag:
COUNTIFS(A:A,B1#,C:C,D1#)
The dynamic arrays in cells B1 and D1 are working fine, and if I change the countifs so only 1 of the criteria refers to a dynamic range (removing the hashtag and selecting 1 of the results in the array) then it works fine. The problem is when I need it to use 2 (or more?) dynamic ranges.
Any thoughts?
答案1
得分: 0
如果项目彼此独立，例如B1不直接与D1相关，而B2直接与D2相关，那么你需要转置其中一个数组。
=COUNTIFS(A:A, B1#, C:C, TRANSPOSE(D1#))
注意：
这将创建一个二维数组，其行数与B1#中的行数相同，列数与D1#中的列数相同。因此，要获得总数，请将其包含在SUM或SUMPRODUCT中。
另外，最大独立数组数量为两个。
此外，这是一个AND情况，所以A中的值必须等于B中的值，同一行中的C中的值必须等于D中的值。
如果想要超过两个，我们需要摆脱COUNTIFS，使用需要涵盖将使用的行数最多的范围而不是完整列的数组类型公式。
=SUMPRODUCT(ISNUMBER(MATCH(A1:A100, B1#, 0)) * ISNUMBER(MATCH(C1:C100, D1#, 0))
这个版本与COUNTIFS类似，其中A和C中的值在同一行中必须存在于其相应的列表中才能计数。如果想要单独计数它们，然后将*更改为+，这将使它成为一个OR。
ISNUMBER(MATCH(A1:A100, B1#, 0))的使用可以根据需要添加多个，需要根据需要更改范围。
英文:
if the items are independent of each other as in B1 does not directly correlate to D1 and B2 directly correlates to D2 then you need to Transpose one of the arrays.
=COUNTIFS(A:A,B1#,C:C,TRANSPOSE(D1#))
Note:
This will create a 2D array with as many rows as in B1# and as many Columns as in D1#. So to get the total wrap it in SUM or SUMPRODUCT.
Also, The max independent arrays are two.
Also that it is an AND situation so the value in A must be equal to the a value in B and the value in C in the same row, must be equal to a value in D
If one wants more than two we need to move away from countifs and use array type formula which will require the use of ranges that encompass the most rows that would be used and not full columns.
=SUMPRODUCT(ISNUMBER(MATCH(A1:A100,B1#,0))*ISNUMBER(MATCH(C1:C100,D1#,0)))
This version is AND like the COUNTIFS where the values in A and C on the same row must exist their corresponding lists to be counted. IF you want to count them individually then change the * to + which makes it an OR.
And the use of ISNUMBER(MATCH(A1:A100,B1#,0)) can be added as many as desired changing the ranges as necessary.
答案2
得分: 0
我不确定 COUNTIFS 在动态数组上是否能正常工作，而你目前正在使用的就是动态数组。
一种解决方法是尝试使用 SUMPRODUCT 函数：
=SUMPRODUCT((A:A=B1#)*(C:C=D1#))
英文:
Edit: This will not work, see below comment by @Scott Craner
I'm unsure if COUNTIFS work well with dynamic arrays, which is what you are currently using.
A workaround can be to try use SUMPRODUCT
=SUMPRODUCT((A:A=B1#)*(C:C=D1#))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论