CODEWITHSUNDEEP
google-sheetscountgoogle-sheets-formulaarray-formulas
LWW
LWW

Reputation: 71

Using COUNTIFS in an array formula

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.

Upvotes: 7

Views: 31277

Answers (2)

Warren Schmidenberg
Warren Schmidenberg

Reputation: 91

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

Upvotes: 4

AdamL
AdamL

Reputation: 24599

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.

Upvotes: 3

Related Questions