Using COUNTIFS in an array formula

Question

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))

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.

My data resembles:

    Column1   Column2    Column3   Result
--------------------------------------------
   apple     green      eaten       x

   orange    orange     noteaten    x

   apple     red        eaten       x

   orange    orange     noteaten    x

   apple     green      eaten       x

...

The x column is where the arrayformula would output.

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.

I don't know much about Google Sheets, but in Excel this would not need to be an array formula, if I understand you correctly. Simply the non-array version copied down would suffice, though personally I don't like to use this intersection method (perhaps this isn't valid in Google Sheets?) and so would prefer =COUNTIFS(I:I,I2,H:H,H2,G:G,G2) in row 2 and copied down. — XOR LX
I forgot to mention that the reason it needs to be open ended columns is because this is calculating the output of a form, which inserts a new row for each submission — LWW

AdamL AdamL · Accepted Answer · 2015-03-23T23:51:45

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

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)))))

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))

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.

Using COUNTIFS in an array formula

1 Answers