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.

Answer 1

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.

Answer 2

This is old but I found two workarounds.

1. Adding an if statement to the ArrayFormula does the trick. E.g.:

   =ArrayFormula(IF(I:I="","",COUNTIFS(I:I,I:I,H:H,H:H,G:G,G:G)))

2. Otherwise, if you can afford to have an extra column, you could combine the data in your three columns and run a COUNITF on that. That said, you'd need to include an if statement in that to exclude the count for empty rows.

   Column K:

   =ArrayFormula(G:G&H:H&I:I)

   In the Result Column:

   =ArrayFormula(IF(I:I="","",COUNTIF(K:K,K:K)))