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
– XOR LX, Commented Mar 23, 2015 at 9:15
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
– LWW, Commented Mar 23, 2015 at 20:11

Warren Schmidenberg · Accepted Answer · 2022-04-06 18:39:51Z

4

This is old but I found two workarounds.

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

answered Apr 6, 2022 at 18:39

Warren Schmidenberg

914 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AdamL · Accepted Answer · 2015-03-24 22:42:16Z

3

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.

edited Mar 24, 2015 at 22:42

answered Mar 23, 2015 at 23:51

AdamL

24.8k7 gold badges73 silver badges61 bronze badges

7 Comments

LWW Over a year ago

Thanks, bit I couldn't get the MMULT to work, could you elaborate on what is actually going on? Either way though, the 3162 row limit may be a deal breaker.

AdamL Over a year ago

Here is a spreadsheet that attempts to explain: docs.google.com/spreadsheets/d/…

AdamL Over a year ago

Also edited to give another option (I tested the MMULT in a ss and it seemed to work though, notwithstanding the limitation I mentioned).

XOR LX Over a year ago

Can you explain the statement: "COUNTIFS can not be iterated over an array, as eg COUNTIF can"? Is this something particular to Google Sheets? Or does it apply to Excel as well? If so, can you give a very small example to illustrate what you mean?

AdamL Over a year ago

@XORLX, good questions, and apologies for not being specific. I was really talking only about Google Sheets in this instance (in fact, it might be appropriate for the OP to remove the excel tag). In Sheets, you can enter a range (or computed array) in the second argument of COUNTIF, invoke as an array formula, and an array output will be automatically produced, where each element in that range is "countiffed". The same cannot be done for the second (and fourth, and sixth etc) arguments in COUNTIFS (for reasons unknown). (tbc)

|

Collectives™ on Stack Overflow

Using COUNTIFS in an array formula

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related