在数组公式中使用COUNTIFS

本文介绍了在数组公式中使用COUNTIFS的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试计算某种给定类型的事物发生的次数，并且我需要这种行为来自动扩展到插入的行.像这样:

I'm trying to count the number of times something of a given type occurs and I need this behaviour to automatically expand to inserted rows. Something like:

=Arrayformula(COUNTIFS(I:I,I:I,H:H,H:H,G:G,G:G))

嵌套的countif公式在单行上使用时将产生正确的值，但当前数组公式一直向下输出1.

The nested countif formula will result in a correct value when used on a single row but currently the array formula is outputting 1 all the way down.

我的数据类似于:

Column1 Column2 Column3 Result -------------------------------------------- apple green eaten x orange orange noteaten x apple red eaten x orange orange noteaten x apple green eaten x

...

x列是arrayformula输出的位置.

The x column is where the arrayformula would output.

X应该浏览所有数据并计算出绿色苹果eaten的数量，下一行将计算noneaten橙色橙子，依此类推.我知道arrayformula不会使用聚合函数，但是在countif的替代项上没有发现任何东西.

X on Row 1 should look through all the data and count up the number of green apples eaten, the next row would count noneaten orange oranges, and so on. I know that arrayformula doesn't take aggregate functions but I didn't find anything on alternatives to countif.

推荐答案

不幸的是，在Google表格中，COUNTIFS不能在数组上进行迭代，例如COUNTIF可以(无论如何，在编写时).

Unfortunately, in Google Sheets, COUNTIFS can not be iterated over an array, as eg COUNTIF can (at the time of writing this, anyway).

您将需要求助于MMULT，例如:

You would need to resort to MMULT, something like:

=ArrayFormula(IF(ROW(G:G)=1,"Result",MMULT((G:G=TRANSPOSE(G:G))*(H:H=TRANSPOSE(H:H))*(I:I=TRANSPOSE(I:I)),SIGN(ROW(G:G)))))

但请注意，Sheets中似乎存在一个限制，即由G:G = TRANSPOSE(G:G)等形成的2D数组不能超过1000万个元素.最多对应3162行.

but be aware there appears to be a limitation in Sheets whereby the 2D array formed by G:G=TRANSPOSE(G:G) etc cannot exceed 10 million elements. This corresponds to a maximum of 3162 rows.

另一种选择是使用字符串串联:

Another option is to use concatenation of strings:

=ArrayFormula(COUNTIF(G:G&CHAR(9)&H:H&CHAR(9)&I:I,G:G&CHAR(9)&H:H&CHAR(9)&I:I))

可以绕开"3162"的限制. CHAR(9)是制表符，但可以是您确定不会在数据中出现的任何字符.

which gets around the "3162" limitation. CHAR(9) is a tab character, but it could be any character that you are certain will not appear in your data.