How to use Sum Product - array formula
Requirement:
Any advice on how to do the calculation below as an array/CSE formula?
| A | B | C | D | E | F | G | H | I |
| 2 | 22 | 20 | 6 | 7 | 10 | 46 | 82 | 80 |
| 3 | 76 | 2 | 100 | 55 | 77 | 61 | 2 | 92 |
| 4 | 23 | 15 | 52 | 16 | 30 | 37 | 18 | 54 |
| 5 | 17 | 30 | 98 | 48 | 49 | 3 | 37 | 54 |
| 6 | 75 | 44 | 24 | 14 | 6 | 94 | 55 | 89 |
| 7 | 99 | 32 | 39 | 49 | 62 | 70 | 6 | 25 |
| 8 | =MAX(B2:B7) | =MAX(C2:C7) | =MAX(D2:D7) | =MAX(E2:E7) | =MAX(F2:F7) | =MAX(G2:G7) | =MAX(H2:H7) | =MAX(I2:I7) |
| 9 | ||||||||
| 10 | =SUM(B8:I8) | <- How do I do this with an array formula? | ||||||
Solution:
=SUMPRODUCT(SUBTOTAL(4,OFFSET(B2:I7,,COLUMN(B2:I7)-MIN(COLUMN(B2:I7)),,1)))
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use SUMIF |
| How to combine LARGE and SUMIF - Array formula |
| How to use SUMPRODUCT |
| How to use IFERROR, ROUND and SUM in one formula |
| How to use SUMIF to extract attendance details from a register |
| How to use SUMIF using Variable Columns |
| How to use SUMPRODUCT with dates |
| How to sum up values in a date range |
| How to sum up columns in each row and highlight until that value |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
