How to Expand Count Results in Google Sheets Like Array Formula Does

0
136
How to Expand Count Results in Google Sheets

There are chances that you want to count the values in a row instead of an entire range and auto repeat the same in other rows. See the below image where I’ve highlighted the formula in red color. Probably you are expecting a single count formula that would return an expanding result like the array formula does. Is it possible? Yes! Then how to use count function to return an array result in Google Sheets? We can learn here how to expand count results in Google Sheets.

With any of the count functions, even using it with an array formula, you can’t get an expanded result. The only solution is the use of MMULT like count.

How to Expand Count Results in Google Sheets Using MMULT

The following MMULT formula, which we are going to use to expand count results in Google Sheets, is also in line with my below tutorials.

1. MMULT Instead of SUMIF in Google Sheets for Array Result

2. How to Do Conditional MMULT in Google Sheets [MMULT with Criterion]

So if you have already learned that, you can just read this tutorial to understand the MMULT formula usage as count. Else you should open a blank Google Sheet and apply the steps that follows.

How to Use Count Function to Return an Array Result in Google Sheets

Sample Data:

Count Function to Return an Array Result in Google Sheets

This is a basic attendance sheet of a company for a week. In this, there is only one formula in cell H2. This formula counts all the row individually and places the total in the last cell. Here normally we use COUNTA as we want to count text. As I told you, since Google Sheets count functions can’t return an expanding array result, I’ve used MMULT here. The MMULT formula below can expand Count results in Google Sheets.

Formula:

=mmult(iferror(ArrayFormula(len(B2:G8)/len(B2:G8)),0),ArrayFormula(transpose(COLUMN($B$2:$G2)^0)))

Note: Please do type the formula in your sheet instead of copying it from here.

Formula Explanation:

To understand this formula you should know the use of MMULT formula in Google Sheets. Please first learn MMULT, then continue.

For your quick reference, here is the syntax of MMULT.

MMULT(matrix1, matrix2)

In our above example, you can see two array formulas. The output of that two formulas just function as matrices in MMULT. What are those formulas?

Formula 1

iferror(ArrayFormula(len(B2:G8)/len(B2:G8)),0)

Again, I’m going to split this formula.

ArrayFormula(len(B2:G8)

This formula would return an array result as below. That means this formula counts the length of the characters in the range and return that result as numeric values. Then again I’ve used the same formula to divide it. This is to just ensure that that formula would always return 1 or 0 even if the length of characters in the cells are more than 1.

create matrix for counta in Google Sheets

Formula 2

ArrayFormula(transpose(COLUMN($B$2:$G2)^0))

This formula simply returns the column numbers of the range B2:G2 in a row. But we want it in a column to use in MMULT to multiply and sum by the first matrix.

So what we did is we transposed this output and further used the caret ^0 symbol to make the column numbers to 1 instead of actual column numbers. This is a practice which I’m following when I want to sum one range as array. Here all the two arrays contain only the number 1. So counting or sum has no difference here.

create matrix from column number in google sheets

That’s all. To understand this tutorial you should create the above sample data and use the formulas as mentioned. This’s the steps to expand Count results in Google Sheets.

Any doubts using this formula, do write to me in comments. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here