First of all, using ISBLANK or LEN with MMULT is not a practical solution to correctly refer to infinite range reference in MMULT function in Google Sheets. In this post, let me share with you a very useful MMULT related tip/formula that will help you to use MMULT in infinite rows in Google Sheets.

Let me clarify why ISBLANK or LEN is not a suitable solution to use MMULT in infinite rows.

We are accustomed to using ISBLANK as well as LEN to limit the expanding of array formula outputs to the rows containing values in any specific column. This will work in MMULT too but there is one limitation.

Before coming to that see the below MMULT formula that sums across rows in a closed/limited range, i.e. A2:C6.

=mmult(A2:C6,{1;1;1})

MMULT Syntax: MMULT(matrix1, matrix2)

Note: When you want to sum across rows using MMULT, use the matrix 2 in MMULT as above. The matrix2 contains only the numbers 1 and the count of the same must be equal to the number of columns in matrix1.

I hope the above formula is simple enough for you to understand. But an expert won’t use the MMULT formula as above. Why?

Just delete any value in the range A2:C6. The MMULT would return an error! To sort out that error, use the MMULT as below.

N Function in MMULT to Sort Out Blank Cell Issue

=ArrayFormula(mmult(n(A2:C6),{1;1;1}))

The N function converts blank cells to 0 while retaining the numbers as it is. The ArrayFormula use is justified as function N is not an array formula.

Now let’s concentrate on our real problem, i.e. how to use MMULT in infinite rows (some people say open range) in Google Sheets.

I want to replace the matrix1 in the above formula which is A2:C6 with an infinite range like A2:C. How to do that?

I have already mentioned above that the ISBLANK or LEN approach won’t work here. First, understand the said two approaches then I will explain how to properly use MMULT in infinite rows or you can say infinite range in Google Sheets.

Methods to Use MMULT in Infinite Rows/Open Range in Google Sheets

LEN with MMULT in Open Range

Normally you may think about using the LEN formula as below with MMULT.

=ArrayFormula(If(len(A2:A),mmult(n(A2:C),{1;1;1}),))

This MMULT formula will definitely work in infinite rows in Google Sheets. But it has a limitation as below.

That means if there is any blank cell between the data range in the first column (the LEN applied column), MMULT will ignore that row in the calculation. The same is the case with ISBLANK with MMULT.

ISBLANK with MMULT in Open Range

Use ISBLANK as below in MMULT to handle infinite Rows in MMULT in Google Sheets, but with the above said limitation.

=ArrayFormula(If(isblank(A2:A)=FALSE,mmult(n(A2:C),{1;1;1}),))

Time to think about a proper formula to use MMULT in infinite rows.

The Proper Way to Use MMULT in Infinite Rows

Formula (this is not my final formula):

=ArrayFormula(mmult(N(array_constrain(A2:C,MATCH(2,1/(A2:A<>""),1),3)),{1;1;1}))

Explanation:

Before going to explain the above formula let me share with you how to populate matrix2 dynamically.

You can replace {1;1;1} with sequence(columns(A2:C2),1)^0. This Sequence generated matrix2 will be useful if your matrix1 has a very large number of columns. I am incorporating this matrix2 formula to the above formula.

=ArrayFormula(mmult(N(array_constrain(A2:C,MATCH(2,1/(A2:A<>""),1),3)),sequence(columns(A2:C2),1)^0))

The only part now left to explain in the formula is the matrix1 which is N(array_constrain(A2:C,MATCH(2,1/(A2:A<>""),1),3))

If you test this formula separately, please include ArrayFormula as below.

=ArrayFormula(N(array_constrain(A2:C,MATCH(2,1/(A2:A<>""),1),3)))

In this, the Match formula returns the number of rows in the range, which is 5.

=ArrayFormula(MATCH(2,1/(A2:A<>"")))

Related Article (but in Excel, equally works well in Sheets): Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel.

Regarding the Array_Constrain, it limits the number of rows in A2:C to 5 (match formula output) rows and 3 columns and that is the MMULT matrix1.

Syntax of ARRAY_CONSTRAIN (to make you understand the formula explanation):

ARRAY_CONSTRAIN(input_range, num_rows, num_cols)

input_range – A2:C
num_rows – MATCH(2,1/(A2:A<>""))
num_cols – 3

To bring more dynamism, you can replace 3 (num_cols) with columns(A2:C2) and the final formula to use MMULT in infinite Rows in Google Sheets is;

=ArrayFormula(mmult(N(array_constrain(A2:C,MATCH(2,1/(A2:A<>""),1),columns(A2:C))),{1;1;1}))

In my example, matrix1 is the open range. In your case, if matrix2 is the open range, you can follow the above matrix1 steps with matrix2 and vice versa. That’s all. Enjoy!