CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
puredevotion
puredevotion

Reputation: 1195

Google Sheets query sum counted columns

I'm trying to count all cells that match a regex patterns. So far I've come up with a solution using a query per column, and then using SUM() to sum the counts over the different columns.

This rather unwieldy, a change in the query means I need to change a lot of cells and partial formulas. Ideally, I have a solution that sums the counts in the query, or something else that better maintainable.

What I have now:

=sum(
  query(I10:I19,"select count(I) where I matches '^[A-Za-z\s]{4,64}$' label count(I) ''"),
  query(J10:J19,"select count(J) where J matches '^[A-Za-z\s]{4,64}$' label count(J) ''"),
  query(K10:K19,"select count(K) where K matches '^[A-Za-z\s]{4,64}$' label count(K) ''"),
  [etc. etc.]
)

What I'd ideally have:

=query(I10:L19,"select sum(count(J),count(K),count(L)) where L matches '^[A-Za-z0-9\s\-]{4,64}$' OR J matches '^[A-Za-z0-9\s\-]{4,64}$' OR K matches '^[A-Za-z0-9\s\-]{4,64}$' label sum(*) ''")

but nesting count()s in sum() seems not to be supported.

Is there a way to achieve something like this?

Note that the label count(X) '' is to remove any label and header, and to ge the result in the first cell.

Upvotes: 1

Views: 632

Answers (2)

marikamitsos
marikamitsos

Reputation: 10573

You could probably replace your query with a completely different formula.

Please try the following one

=ArrayFormula(COUNTIF(REGEXMATCH(I10:K19,"^[A-Za-z\s]{4,64}$"),true))

If you really insist on using a query try the following

=query(flatten(I10:K19),"select count(Col1) where Col1 matches '^[A-Za-z\s]{4,64}$' label count(Col1) ''")

(The regex is just a copy of the one you shared. If still in need, please share a test sheet.)

Functions used:

Upvotes: 2

iansedano
iansedano

Reputation: 6481

You can use string concatenation

The query is just a string, so you could make your regex query live in a separate cell, which would make your formula look like this, if your regex was in a sheet called "Regex" and in the cell A1 with the value ="'^[A-Za-z\s]{4,64}$'"

=sum(
  query(I10:I19,"select count(I) where I matches" & Regex!A1 & "label count(I) ''"),
  query(J10:J19,"select count(J) where J matches" & Regex!A1 & "label count(J) ''"),
  query(K10:K19,"select count(K) where K matches" & Regex!A1 & "label count(K) ''"),
  [etc. etc.]
)

Reference

The value for query must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

Upvotes: 2

Related Questions