CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
onit
onit

Reputation: 2372

How to convert this FILTER + COUNTA() into an Arrayformula on Google Sheets?

I got this one working fine, but it'd have to be an arrayformula:

=if(A3="","", COUNTA(filter(D:D,regexmatch(LOWER(D:D), LOWER(A3)))))

I have tried it like this, but it gives a total only in the cell the formula sits:

=ARRAYFORMULA(if(A3:A="","", COUNTA(filter(D:D,regexmatch(LOWER(D:D), LOWER(A3:A))))))

Here's the demo file.

Upvotes: 1

Views: 159

Answers (1)

player0
player0

Reputation: 1

try:

=QUERY(D3:D, 
 "select D,count(D) 
  where D is not null 
  group by D 
  label count(D)''")

enter image description here

update 1:

=ARRAYFORMULA(QUERY({FILTER(D3:D, D3:D<>""), 
 FLATTEN(INDEX(QUERY(TRANSPOSE(REGEXMATCH(FILTER(D3:D, D3:D<>"")&"", ""&
 TRANSPOSE(FILTER(A3:A, A3:A<>"")))*1), 
 "select "&TEXTJOIN(",", 1, "sum(Col"&SEQUENCE(COUNTA(D3:D))&")")),2))}, 
 "select Col1,sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"))

enter image description here

update 2:

=ARRAYFORMULA(FLATTEN(INDEX(QUERY(TRANSPOSE(IF(A3:A="",,
 REGEXMATCH(IF(A3:A="",,TRANSPOSE(FILTER(D3:D, D3:D<>""))), A3:A)+0)), 
 "select "&TEXTJOIN(",", 1, "sum(Col"&SEQUENCE(COUNTA(A3:A))&")")), 2)))

enter image description here

update 3:

if column A contains empty cells in between

=ARRAYFORMULA(IFERROR(1/(1/FLATTEN(INDEX(QUERY(TRANSPOSE(IF(A3:A="",,
 REGEXMATCH(IF(A3:A="",,TRANSPOSE(FILTER(D3:D, D3:D<>""))), A3:A)+0)+0), 
 "select "&TEXTJOIN(",", 1, "sum(Col"&SEQUENCE(MAX((A3:A<>"")*ROW(A3:A))-ROW(A3)+1)&")")), 2)))))

enter image description here

update 4:

one more alternative

=INDEX(IFERROR(1/(1/LEN(SUBSTITUTE(TRIM(FLATTEN(QUERY(TRANSPOSE(IFERROR(1/(1/IF(A3:A="",,
 REGEXMATCH(IF(A3:A="",,TRANSPOSE(FILTER(D3:D, D3:D<>""))), A3:A)+0)))),,9^9))), " ", )))))

enter image description here

Upvotes: 1

Related Questions