Excel Dynamic Array Function to Sum Repeated Numbers

Question

I have the following data in Excel:

The real data will go on for over 100 columns.

What I want to get is a sum of the number if it were to repeat horizontally by the number of repeats. NB: Technically I'm repeating one less time, but I'm not wanted to split hairs.

Here's what the expansion would look like if I were to create multiple rows:

I then can just sum those rows:

What I'd like is a dynamic array function that lets me sum the values without the intermediate rows. It's doing my head in.

Here's what I'd like it to look like:

I've tried to start with =BYCOL(R12C23:R12C,lambda(x, but am totally stuck at how to compute the result.

Can anyone suggest how to complete the formula or offer an alternative?

Answer 1

Here is one option:

Formula in B3:

=LET(x,B2:E2,y,COLUMN(x),z,TOCOL(y),TOROW(BYROW((z<=y+x-1)*(z>=y)*B1:E1,SUM)))

Answer 2

Try

=MMULT(B1:E1,LET(n,COLUMNS(B1:E1),x,SEQUENCE(n,,0,-1)+SEQUENCE(,n),(x>=0 )*(x<=TRANSPOSE(B2:E2))))

or:

=MAP(COLUMN(B1:E1)-1,LAMBDA(x,SUM(TAKE($B1:E1*(SEQUENCE(,x,x,-1)<=B2:E2),,x))))

Answer 3

I haven't had chance to sit down and work through this properly but my idea (very similar to @JvdV and @Dang D. Khanh) was just to work across a single row including all the values that were within range for each column:

=LET(values,B1:E1,repeats,B2:E2,seq,SEQUENCE(1,COLUMNS(values)),
MAP(seq,LAMBDA(c,SUM(values*(c>=seq)*(c<seq+repeats)))))

---

The equivalent formula for displaying one cell at a time would be:

=LET(values,$B$1:$E$1,repeats,$B$2:$E$2,seq,SEQUENCE(1,COLUMNS(values)),col,COLUMN()-1,
SUM(values*(col>=seq)*(col<seq+repeats)))

or to show result for the column to the left of the current cell:

=LET(values,$B$1:$E$1,repeats,$B$2:$E$2,seq,SEQUENCE(1,COLUMNS(values)),col,COLUMN()-2,
SUM(values*(col>=seq)*(col<seq+repeats)))

However I don't like the mix of column numbers and sequence running from 1 to 4 because it would only work if starting in column B.

Maybe better to use column numbers only like this:

=LET(values,$B$1:$E$1,repeats,$B$2:$E$2,seq,COLUMN(values),col,COLUMN(),
SUM(values*(col>=seq)*(col<seq+repeats)))

and

=LET(values,$B$1:$E$1,repeats,$B$2:$E$2,seq,COLUMN(values),col,COLUMN()-1,
SUM(values*(col>=seq)*(col<seq+repeats)))

which are not vulnerable to insertion/deletion of columns to the left of the data.