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]

I tried to select the 3 horizontal columns and enter the formula.
[....]
B79:D79 {=SUM(INDEX(B73:D76,0,{1;2;3}))}

Be mindful of the meaning of the different separators in array constants. Also be mindful of localization differences (period v. comma for decimal point; comma v. semicolon to separate parameters; etc).

I am using US languages rules.

The semicolon separates rows in array constants. (I believe that is true for all languages.) So you are entering a vertical array formula into a horizontal range. In that context, only the first row of the array formula is selected and duplicated across the horizontal range.

My original horizontal formula uses comma to separate columns in the array constant. (Caveat: I believe some languages substitute another character for comma in this context: back-slash?) So I enter a horizontal array formula into a horizontal range.

In contrast, my vertical formula uses semicolon. But correspondingly, I select a vertical range.

 

[joeu2004]

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

For real?! Thanks for that heads-up. I don't know beans about Office 365, not to mention the ever-changing world of insider ("experimental") features.

 

[XOR LX]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<tbody>[TR]
[TH][/TH]
[TD="align: left"]{=SUM(INDEX(B73:D76,0,{1;2;3}))}[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Read joeu2004's posts again. You've transposed the required array. You should be using {1,2,3} for a horizontal array, not {1;2;3}.

Regards

 

[joeu2004]

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

"Contorted"? "Fool Excel"?

You once posted the following (brilliant!) solution:

=XIRR(INDEX($C:$C,N(IF(1,(MODE.MULT(IF($A$2:$A$47=F2,{1,1}*ROW($A$2:$A$47))))))),
INDEX($B:$B,N(IF(1,MODE.MULT(IF($A$2:$A$47=F2,{1,1}*ROW($A$2:$A$47)))))))

You explained that you empirically discovered that the extra syntax was necessary to make it work.

Is that not "contorted"? Aren't you "fooling" (coercing would be a better word) Excel into doing the right thing?

(Rhetorical questions.)

 

[XOR LX]

Is that not "contorted"? Aren't you "fooling" (coercing would be a better word) Excel into doing the right thing?

Guilty! And here's to contortion and coercion in all their myriad forms!

Merry Christmas to all!

 

[BrianGGG]

Thanks All. I once again mixed up the blasted semi-colon vs. comma as suspected. I fixed that, and now both the {1,2,3} and SEQUENCE formats work.