Sum of Matrix Rows or Columns Using Sumif in Google Sheets

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?

Total Column and Total Row - Google Sheets Formula
image – 1

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.

Sum of Matrix Rows Using SUMIF
image – 2

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))
Sum of Matrix Columns Using SUMIF
image – 3

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:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.