Array of SUMs?

[BrianGGG]

Hello.

I have a simple set of numbers as below:

	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]73[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]75[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]76[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

</tbody>

Summary​

I'd like to return an array of column sums rather than the grand total sum of all numbers, e.g. my desired answer is {24;26;30} rather than 80.
However, any syntax that I give to sum (e.g. SUM(b73:d76), etc.) gets mashed up into a single total.

My goal is to write a single formula to "spill" across the columns with the new dynamic arrays (rather than copy the formula across). Any array function (with or without the new dynamic array functions like SEQUENCE) will work for me.

Any ideas?


thanks
BrianGGG

 

[joeu2004]

Array-enter (press ctrl+shift+Enter instead of just Enter) a formula that contains the following:

SUM(INDEX(B73:D76,0,{1,2,3}))

or more generally:

SUM(INDEX(B73:D76,0,COLUMN(B73:D73)-COLUMN(A73)))

But that produces a horizontal array of 3 columns, the equivalent of {24,26,30}.

The syntax {24;26;30} is a veritcal array of 3 rows. Do you require the vertical array?

 

[XOR LX]

Array-enter (press ctrl+shift+Enter instead of just Enter) a formula that contains the following:

SUM(INDEX(B73:D76,0,{1,2,3}))

or more generally:

SUM(INDEX(B73:D76,0,COLUMN(B73:D73)-COLUMN(A73)))

Have you tested these?

Both resolve to simply:

=SUM(B73:B76)

and not to how you would wish.

You can use SUBTOTAL and OFFSET here, though I prefer the non-volatile

MMULT(TRANSPOSE(ROW(B73:D76)^0),B73:D76)

which, suitably coerced (i.e. within some external function and committed with CSE), will produce:

{24,26,30}

which can be transposed if desired.

Regards

 

[joeu2004]

Array-enter (press ctrl+shift+Enter instead of just Enter) a formula that contains the following:
SUM(INDEX(B73:D76,0,{1,2,3}))
or more generally:
SUM(INDEX(B73:D76,0,COLUMN(B73:D73)-COLUMN(A73)))

Have you tested these?

I did. In Excel 2010, I select 3 horizontal cells and array-enter either formula (press ctrl+shift+Enter instead of just Enter). The result is 24, 26 and 30 respectively.

BTW, we can get a vertical array by selecting 3 vertical cells and array-entering the following:

=SUM(INDEX(B73:D76,0,{1;2;3}))
or:
=SUM(INDEX(B73:D76,0,TRANSPOSE(COLUMN(B73:D73)-COLUMN(A73))))

I was surprised that =TRANSPOSE(SUM(INDEX(B73:D76,0,{1,2,3}))) does not work, array-entered of course.

PS.... Having said and demonstrated as much, I should say that nevertheless, there might be contexts where embedding the expression SUM(INDEX...) does not work, even when array-entered. We both know that there are expressions where Excel inexplicably does not treat the embedded expression as an array. XOR LX is expert at finding creative, albeit contorted, ways to fool Excel into delivering embedded arrays.

 

[BrianGGG]

Unfortunately, I am using the Insider Edition of Excel 2016 along with Dynamic Arrays.
There is no such thing as CSE anymore ...

And the following: SUM(INDEX(B73:D76,0,{1;2;3}))
does not work for me.

I have been trying to use some coercion myself, most notably the N(IF(1))) tricks...and none of them seem to return the right values.
I think SUM is a lost cause here, and I'm hoping/praying that a future version of dynamic arrays works more like TABLES and can be totaled and formatted, etc.


Thanks All...I'll keep trying


BrianGGG

 

[BrianGGG]

Here's what I get with the array formula:

	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]73[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]75[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]76[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]79[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B79[/TH]
[TD="align: left"]{=SUM(INDEX(B73:D76,0,{1;2;3}))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

I did. In Excel 2010, I select 3 horizontal cells and array-enter either formula (press ctrl+shift+Enter instead of just Enter). The result is 24, 26 and 30 respectively.

Apologies. I thought you were referring to a single-cell array formula (as I was in my solution), not to a multi-cell array formula.

XOR LX is expert at finding creative, albeit contorted, ways to fool Excel into delivering embedded arrays.

"Contorted"? "Fool Excel"?

Cheers

 

[XOR LX]

Thanks All...I'll keep trying

What issues did you face with the solution I proposed?

Regards

 

[BrianGGG]

Sorry if I messed this up.

I tried to select the 3 horizontal columns and enter the formula. If I try to delete one of the cells, it says that I can't change an array, so I think I succeeded. Yet:

	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]73[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]75[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]76[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]79[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]24[/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B79:D79[/TH]
[TD="align: left"]{=SUM(INDEX(B73:D76,0,{1;2;3}))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[BrianGGG]

Sorry for the multiple replies...

Interestingly enough, the following DOES work:

	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]73[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]74[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]75[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]76[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]77[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]78[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]79[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B79:D79[/TH]
[TD="align: left"]{=SUM(INDEX(B73:D76,,SEQUENCE(1,3)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]