Search code examples
google-sheetssumtransposegoogle-sheets-formulaarray-formulas

Multiple column SUMS with single formula


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?

My sheet

enter image description here


Solution

  • =ARRAYFORMULA(TRANSPOSE(MMULT(TRANSPOSE(IF(B2:5<>"", B2:5, 0)), ROW(B2:5)^0)))
    

    0