How to use array formula to return an array of sums in Excel

Question

eg, we have

I would like a array formula that returns an ARRAY = {7,12,14,11} Note it is not text, it is an array can be used in other array formula.

Currently i am using {=A1:A4+B1:B4+C1:C4+D1:D4}, however if the number of column is huge I cannot handle it manually.

@colinfang Is it possible for you to add a sum column (even if temporarily) to your data then grab an array from that? I would think this would be a very easy solution. — Gaffi
– Gaffi, Commented Mar 13, 2012 at 14:31
@Gaffi I wish the formula would dynamically reference to the data source without any temp cells. In your way if the temp column is deleted and data source is changed, then the outcome would be wrong — colinfang
– colinfang, Commented Mar 13, 2012 at 14:38

lori_m · Accepted Answer · 2012-03-13 14:51:44Z

18

I think this evaluates to the required array and could be extended to larger ranges:

=MMULT(A1:C4,TRANSPOSE(COLUMN(A1:C4)^0))

answered Mar 13, 2012 at 14:51

lori_m

5,5751 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

barry houdini Over a year ago

I like it! +1 - possible alternative =SUBTOTAL(9,OFFSET(A1:C4,ROW(A1:C4)-ROW(A1),0,1))

colinfang Over a year ago

thanks you, I use this finally: MMULT(A1:C4,TRANSPOSE(IF(A1:C1,1,1)))

Pete Over a year ago

To sum the columns instead: =TRANSPOSE(MMULT(TRANSPOSE(A1:C4),IF(A1:A4,1,1)))

airstrike Over a year ago

And how would you go about it if you wanted to skip some rows? Say you wanted A1:C1, A2:C2 and A4:C4, for example.

Oken Over a year ago

Thanks for sharing. This is a lifesaver. Been thinking of how to do this with the new spill functions.

Tarion · Accepted Answer · 2012-03-13 14:45:11Z

1

When your matrix starts at A1 you can use:

=VERKETTEN("{";SUMME(A1:C1); ",";SUMME(A2:C2);",";SUMME(A3:C3);",";SUMME(A4:C4);"}")

This is of course for the german version. But "SUMME" should be SUM and "VERKETTEN" should be CONCATENATE. I'm sure you will find it in the help.

then it will be:

=CONCATENATE("{";SUM(A1:C1); ",";SUM(A2:C2);",";SUM(A3:C3);",";SUM(A4:C4);"}")

Even shorter:

="{"&SUM(A1:C1)&","&SUM(A2:C2)&","&SUM(A3:C3)&","&SUM(A4:C4)&"}"

For more you might need to use scripts that can handle loops etc.

edited Mar 13, 2012 at 14:45

answered Mar 13, 2012 at 14:34

Tarion

17.3k13 gold badges79 silver badges113 bronze badges

Collectives™ on Stack Overflow

How to use array formula to return an array of sums in Excel

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related