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

GiGiKing·March 26, 2024·0 comments

If you’ve ever wondered how to expand count results in Google Sheets, I have a game-changing solution for you! Forget about using any Count functions, because the answer lies in the mighty MMULT function.

While most Count functions return a single-cell output from an array, they fall short when it comes to counting each row individually. Countif and Countifs are exceptions to this, but they are not suitable for the task at hand.

But wait, I have an update for you! Now, you can expand count functions using Lambda. I’ll explain this solution later, but first, let’s understand the concept of Row Count vs. Each Row Count in Google Sheets.

Row Count:

Row Count refers to a non-array formula that counts the values in a single row. It doesn’t expand to cover multiple rows. Check out the example below:
Row Count - Non expanding formula

Each Row Count:

Each Row Count, on the other hand, aims to return a count of values in each row. This is what we want to achieve – a single formula that gives us the count result in each row. Take a look at the image below to see the difference:
Each Row Count - Non expanding formula

Now that we have a clear understanding of what expanding count results means, let’s dive into the solution using MMULT.

To replace multiple count formulas with a single formula in Google Sheets, you can use the powerful MMULT function. It allows you to expand count results just like an array formula does. Exciting, right?

Please note that you can now use the BYROW function with the Count functions to return an array result in Google Sheets. Scroll down to see an image where I’ve highlighted the single formula in red that returns the count result in each row. MMULT is the key function used in that formula.

To expand count results in Google Sheets using MMULT, follow these simple steps:

First, let’s take a look at some sample data. We have a basic attendance sheet recorded for a week. In this case, there is only one formula in cell H2, which counts all the rows individually and places the total in the last cell of each row.
Usually, we might use the COUNTA formula in cell H2 and drag it down to count the text. However, we’ll use MMULT since count functions in Google Sheets couldn’t return an expanding array result.
Here is the MMULT formula that can expand count results in Google Sheets:

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

This formula counts both numbers and text strings in each row and expands the result.

Let’s break down the formula to understand how it works:

The first part of the formula, iferror(ArrayFormula(len(B2:G8)/len(B2:G8)),0), uses the LEN function to count the length of the characters in the range B2:G8. It returns a numeric value indicating the length of each cell.
Next, we divide the first LEN output by itself to ensure that it always returns 1 or 0. If the length of characters or numbers in a cell is one or more, the formula returns 1; otherwise, it returns 0.
The second part, ArrayFormula(transpose(COLUMN($B$2:$G2)^0)), returns the column numbers 2 to 7 of the range B2:G2 horizontally. We use the COLUMN function to obtain the column numbers and the caret (^0) to convert them into 1 instead of actual column numbers. To use MMULT, we need to transpose this output to make it one column and 6 rows.

By multiplying the two matrices, which both contain the number 1, we get the count or sum of values in each row.

And that’s it! By following these steps, you can expand count results in Google Sheets using MMULT.

Expanding Count Results in Google Sheets Using BYROW

Previously, we used the MMULT solution because we had no way to expand the counta(B2:G2) formula in each row. However, we can now use the BYROW function to achieve this.

To expand count results in Google Sheets using BYROW, simply insert the following formula in cell H2:

=byrow(B2:G,lambda(row,counta(row)))

Voila! Your count results will be expanded in each row.

If you prefer to skip blank rows, you can modify the formula as follows:

=byrow(B2:G,lambda(row,if(counta(row)=0,,counta(row))))

And there you have it! A new way to expand count results in Google Sheets using BYROW.

Conclusion

Expanding count results in Google Sheets is now easier than ever. Whether you choose to use MMULT or BYROW, you have the power to count values in each row with just a single formula. Say goodbye to multiple count formulas and hello to efficiency and simplicity.

If you have any doubts or need further assistance with these formulas, please leave a comment. Enjoy exploring the possibilities of count functions in Google Sheets!

Resources: