Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

eg, we have

 1  2  4
 3  4  5
 4  5  5
 2  4  5

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.

like image 835
colinfang Avatar asked Mar 13 '12 14:03

colinfang


People also ask

How do you use an array formula in Excel?

Enter an array formulaSelect the cells where you want to see your results. Enter your formula. Press Ctrl+Shift+Enter. Excel fills each of the cells you selected with the result.

How do I apply an array to multiple cells in Excel?

Steps to enter a multi-cell array formulaSelect multiple cells (cells that will contain the formula) Enter an array formula in the formula bar. Confirm formula with Control + Shift + Enter.

How do I create a dynamic array in Excel?

A dynamic array formula is entered in one cell and completed with a regular Enter keystroke. To complete an old-fashioned array formula, you need to press Ctrl + Shift + Enter. New array formulas spill to many cells automatically. CSE formulas must be copied to a range of cells to return multiple results.


1 Answers

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

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

like image 164
lori_m Avatar answered Sep 30 '22 05:09

lori_m