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?

Sherl

What a nice question! It shouldn't be too bad to create the 2D array using reduce/hstack for each row then reduce/vstack to stack them and finally get the column totals. The challenge for me is that you shouldn't have to actually create the 2D array...there should be a more direct way of creating the column totals just in a 1D array. – Calvinna

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)))

Component

Yes that is exactly the type of solution I was working towards. – Calvinna

@TomSharpe, let me know what you come up with! I feel this is still very verbose. – Component

@Component - Thank you for this. I'm getting a #NAME? error. =LET(x,R13C23:R13C26,y,COLUMN(x),z,TOCOL(y),TOROW(BYROW((z<=y+x-1)*(z>=y)*R12C23:R12C26,SUM))) – Sherl

@Enigmativity, I've used the ETA LAMBDA syntax where we could skip over the LAMBDA(r,SUM(r)) part and sum directly. You might want to fiddle that back in to see if that resolves the error. Furthermore, please note that I deduct 1 in my formula due to the fact there is one column to the left of the start of the array. Adjust your formula accordingly. – Component

It works for me. I will give it bit more thought but doubt I will come up with anything shorter TBH ! – Calvinna

It works when I use LAMBDA(r,SUM(r)). Thank you. – Sherl

Genius! What did you mean by "deduct -1" you mentioned in a comment? It works anywhere i.e. you could use SEQUENCE(COLUMNS(x)) instead of COLUMN(x). – Hyperkinesia

@VBasic2008, yeah that's right! – Component

Can I trouble you to see if you can convert this to a single cell result that doesn't spill? I need it to work from the current cell to the left. – Sherl

@Sherl just add the '@' operator for implicit intersection to return the first value in an otherwise spilled array of values. – Component

@Component - It's not the first value I need. It's the last one. – Sherl

Ahh, then nest the formula in TAKE(......,-1) – Component

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))))

Grekin

Can I trouble you to see if you can convert this to a single cell result that doesn't spill? I need it to work from the current cell to the left. – Sherl

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.

Calvinna

Can I trouble you to see if you can convert this to a single cell result that doesn't spill? I need it to work from the current cell to the left. – Sherl