In this post, i.e. Sum of matrix rows or columns in Google Sheets, we can learn how to get a total row or total column using the Sumif function.

In pivot tables, you may have seen a total row, column, or both. In a normal table, array, or range, we can get such a total row/column or both using Sumif.

Normally for the sum of matrix rows (a total column at the right of a range) or sum of matrix columns (a total row at the bottom of a range), we use the MMULT function in Google Sheets. I’ve already explained part of the same – How to Sum Each Row in Google Sheets.

The same we can get with a smarter Sumif formula too.

Let’s learn how to get the sum of matrix rows or the sum of matrix columns using the Sumif in Google Sheets.

Introduction: Sumif to Get the Sum of the Elements in Each Row or Column (Matrix Sum)

As you may know, the role/purpose of the Sumif function in Google Sheets or Excel is to conditional sum across a range.

How can we use it then for the sum of elements in each row or column since there are no conditions involved?

Let me start with the Sumif logic first.

Sumif Logic – Virtual ‘Range’ and ‘Criterion’

In the past, I have posted a tutorial about multiple sum columns in Sumif. In that, I’ve explained to you how to get a total of two or more columns by duplicating the ‘range’ is Sumif.

The same logic we will follow here. But remember. We have no ‘range’ or ‘criterion’ this time. We have only ‘sum_range’, i.e. the matrix C3:F7.

To understand the above-mentioned arguments/parameters, see the syntax of the Sumif, conditional sum function, first.

Syntax: SUMIF(range, criterion, [sum_range])

Logic:

We will use row numbers as both the range and criteria for getting the sum of matrix rows (sum of the elements/values in each row) using Sumif in Google Sheets.

For getting the sum of matrix columns (sum of the elements in each column) we will use the column numbers as the range and criteria.

I’ll explain it below with formula and pictures.

Sum of Matrix Rows Using Sumif in Google Sheets

As you have seen above, the Sumif function has 3 arguments. In our solution, the first 2 arguments (range and criterion) will be virtual arguments. The last argument, which is ‘sum_range’, is C3:F7.

We need the sum of matrix rows in G3:G7. That data is in the row numbers 3 to 7. Please see the above image.

The above matrix in C3:F7 (sum_range) has 5 rows and 4 columns. So our range in Sumif to get the sum of matrix rows should also contain a similar number of rows and columns.

What we want to do is to repeat the row numbers of the ‘sum_range’ in 4 columns. The below IF, Column, and Row combination return the virtual Sumif range.

=ArrayFormula(if(column(C3:F3),row(C3:F7)))

Since the range contains row numbers, the Sumif criteria should also be the same row numbers, but only in one column.

=ArrayFormula(row(C3:F7))

I have illustrated the same below.

So the formula to get a total column on the right, the sum of matrix rows, or we can say the sum of the matrix elements in each row will be;

=ArrayFormula(sumif(if(column(C3:F3),row(C3:F7)),row(C3:C7),C3:F7))

The above formula is in cell G3 (please refer to image – 1)

Sum of Matrix Columns Using Sumif in Google Sheets

Our matrix (sum_range) C3:F7 has 4 columns that are the column numbers 3 to 6 (C to F).

To get the sum of elements in each column, the Sumif ‘range’ must be a range that is equal to the size of the ‘sum_range’.

So to get the range, repeat the column numbers 5 times (there are 5 rows in the matrix).

=ArrayFormula(if(row(C3:C7),column(C3:F7)))

The criteria will be the column numbers itself.

=ArrayFormula(column(C3:F7))

So the formula to get a total row at the bottom of a matrix, the sum of matrix columns, or we can say the sum of the matrix elements in each column will be;

=ArrayFormula(sumif(if(row(C3:C7),column(C3:F7)),column(C3:F3),C3:F7))

The above formula is in cell C8 (please refer to image – 1)

Conclusion

I have several SUMIF and MMULT tutorials on this blog. I’m linking a few of them under the “Additional Resources” below.

For more such tutorials, please search using the search icon on the navigation bar.

That’s all. Enjoy!

Additional Resources:

• MMULT Instead of SUMIF in Google Sheets for Array Result.
• How to Do Conditional MMULT in Google Sheets [MMULT with Criterion].
• Array Formula to Sum Multiple Columns in Google Sheets and Grouping.
• Sum Multiple Columns Dynamically Across Rows in Google Sheets.
• SUMIF Returns Multiple Columns in Google Sheets.
• Sumifs Array Formula Expanding Issue and Alternative Formulas.