I need to SUM
multiple columns (sum for each column, not a total range sum) with a single formula. So the output would look something like this:
+-------+-------+------------+-----------+------------+
| 2019 | 2018 | 2017 | 2016 | 2015 |
+-------+-------+------------+-----------+------------+
| $0.00 | $0.00 | $4,341.00 | $0.00 | $5,281.00 |
| $0.00 | 0 | 0 | 0 | 0 |
| $0.00 | 0 | $10,805.00 | $2,865.00 | $8,295.00 |
| $0.00 | 0 | 0 | 0 | $233.00 |
+-------+-------+------------+-----------+------------+
| $0.00 | $0.00 | $15,146.00 | $2,865.00 | $13,809.00 |
+-------+-------+------------+-----------+------------+
I've tried several approaches (SUM
,SUMIF
,SUMIFS
,MMULT
), but can't seem to get it right. The closest I've come is with this formula from a website I found
=ArrayFormula(MMULT(B2:F5,(transpose(COLUMN(B1:F1)^0))))
I would also prefer to avoid the need for a 0 value as shown in the MMULT
attempt below. But, if that's what it takes to make it work, so be it. But a blank value would be preferred. Am I attempting the impossible or just looking in the exact wrong direction?