ArrayFormula to automatically calculate one column

Question

I want an array formula to use with Google Forms data to automatically calculate running metrics on my data.

In this case, in column Q, AE and AS I want it to auto-calculate whenever new responses come in.

I'm trying to use this formula below but isn't working for all column, just for the first line.

={arrayformula(if(len(AF3:AF);SUM(vlookup(AF3:AR3;$A$1100:$B$1101;2;0));))}

I want a sum of all words that contain "Verdadeiro" in a row.

What I'm doing wrong?

My Google Sheet:

Hi if the existing answer doesn't resolve your issue, could you please include your expected output in your sample sheet? I can't fully understand your goal particularly this "sum of all words" — Kristkun
– Kristkun, Commented Aug 31, 2021 at 22:13

player0 · Accepted Answer · 2021-08-31 21:15:40Z

1

try in row 1:

={"Total AG";""; ARRAYFORMULA(IF(A3:A="";;
 MMULT(N(REGEXMATCH(A3:AR&""; "Verdadeiro")); SEQUENCE(COLUMNS(A3:AR))^0)))}

answered Aug 31, 2021 at 21:15

player0

131k14 gold badges91 silver badges149 bronze badges

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

2 Comments

Perfect! You are the best! Thanks!

man... sorry but could you help me with that too hehe :) stackoverflow.com/questions/69531907/…