I have a list of users in Column F and in Row 1 a list of dates. I want to use ARRAYFORMULA to sum the values from relevant columns per each user. As an example, this sums 4 columns (F,G,H,I) per user: =ARRAYFORMULA(IF(LEN(F1:F),G1:G H1:H I1:I J1:J,""))

My question is, if it possible to sum for a dynamic number of columns. For example, I'll choose a number (e.g 7, 30...) and it will sum the relevant number of columns. Can this be done?

Here's a spreadsheet with the above data: https://docs.google.com/spreadsheets/d/17hyBEF1va4GMYZUFkDxxjJ0pXH2oCccgIaBT79GIsGc/edit#gid=0

In A2 I choose how many columns, and it will sum the relevant number of columns. In C1 I use such a formula to sum 4 columns using ARRAYFORMULA as an example (which is static, not dynamic).

CodePudding user response：

There are 3 parameters:

• A2: no of cols
• G2: top left cell of values
• F:F: col of row field (to count number of rows)

=ArrayFormula(MMULT(N(INDIRECT(CELL("address",G2)&":"&ADDRESS(COUNTA(F:F),COLUMN(G2) A2-1,4))),N(TRANSPOSE(COLUMN(INDIRECT(CELL("address",G2)&":"&ADDRESS(COUNTA(F:F),COLUMN(G2) A2-1,4)))^0))))