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.
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.
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.
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.
I think this evaluates to the required array and could be extended to larger ranges:
=MMULT(A1:C4,TRANSPOSE(COLUMN(A1:C4)^0))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With