HomeGoogle DocsSpreadsheetHow to Expand Count Results in Google Sheets Like Array Formula Does

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

Published on

If you ask me how to expand count results in Google Sheets, my answer would be using the function MMULT, not using any Count functions!

Most of the Count functions take an array and return a single-cell output. Countif and Countifs are exceptions to this but are not suitable for each row count.

Update:- We can now expand count functions using Lambda. I’ll add that solution immediately after the MMULT formula.

First, let’s try to understand Row Count Vs. Each Row Count.

Row Count:

Row Count - Non expanding formula

Each Row Count:

(This is a non-array formula. I’ll come to the array formula later on.)

Each Row Count - Non expanding formula

Expanding count results means a single formula that returns the count of values in each row.

When we consider the above example, I want to replace the multiple count formulas (see the formulas in G2:G4) with a single formula in cell G1.

Probably you are expecting a single formula that would return an expanding count result in Google Sheets as the array formula does.

It’s possible with MMULT!

Please note that you can now use the BYROW function with the Count functions to return an array result as above in Google Sheets.

Please scroll down to see the image where I’ve highlighted the single formula in Red color that returns the count result in each row. MMULT is the key function used in that formula.

You can learn here how to expand the count results in Google Sheets using MMULT and BYROW.

As a side note, if you want to include criteria, then you may please switch to this tutorial: Countif Across Columns Row by Row – Array Formula in Google Sheets.

Expanding Count Results in Google Sheets Using MMULT

The following MMULT formula, which we can use to expand count results in Google Sheets, is also in line with my below tutorials. I hope you will find some leisure time to check them too.

If you have already gone through these tutorials, you can just read this tutorial to understand the MMULT formula usage as count. Else you may please open a blank Google Sheets Spreadsheet and apply the steps given below.

Sample Data:

Count Function to Return an Array Result in Google Sheets

It is a basic attendance sheet recorded for a week. In this, there is only one formula in cell H2. That formula counts all the rows individually and places the total in the last cell in each row.

Usually, we may use the following COUNTA in cell H2 and drag down it as we want to count text.

=counta(B2:G2)

I have used MMULT since Google Sheets count functions couldn’t return an expanding array result.

The below MMULT formula can expand Count results in Google Sheets.

MMULT Formula to Count Values in Each Row

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

Here is a more polished one, but I will explain the above formula in detail under the formula explanation section below.

=ArrayFormula(
     if(len(A2:A),(
        mmult(
           iferror(len(B2:G)/len(B2:G),0),
           transpose(COLUMN($B$2:$G2)^0))
        ),
     )
)

What is the difference between the above two formulas?

In the second formula, to work in infinite rows, I have modified the array reference B2:G8 to B2:G. Further moved the ArrayFormula to the front, so only one ArrayFormula is required.

In addition to these, I have added if(len(A2:A) to the formula to control blank rows. Because we are using infinite rows as the range.

The MMULT in Sheets is prone to return errors if blank cells are in the range (matrices).

Note:– The above formulas will count both numbers and text strings in each row and expand the result.

Formula Explanation

To understand this formula, you should know the use of the 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 those two formulas functions as matrices in MMULT. What are those formulas?

Formula 1 as Matrix 1

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

This formula would return an array result as below.

create matrix for counta in Google Sheets

The Len formula counts the length of the characters in the range and returns that result as numeric values in each cell.

Then again, I’ve used the same formula to divide the first Len output. It is to ensure that it would always return 1 or 0.

If the length of characters or numbers in the cells is one or more than one, the formula returns one, else zero.

Formula 2 as Matrix 2

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

The inside Column formula returns the column numbers 2 to 7 of the range B2:G2 in a row (horizontally). I have used the caret ^0 to convert that returned column numbers to 1 instead of actual column numbers.

We want the above output in a column (vertically) to use in MMULT. Why?

Matrix 1 (formula 1) contains 6 columns. As a standard in matrix multiplication (MMULT), the total number of columns in matrix-1 must be equal to the number of rows in matrix-2.

So what I have done is transposed the column formula output to make it one column and 6 rows output.

create matrix from column number in google sheets

Please note that both matrices only contain the number 1. So the matrix product of two matrices will be equal to the count or sum.

That’s all. Follow the steps carefully to get expanding Count results in Google Sheets.

Expanding Count Results in Google Sheets Using BYROW (New)

We have been using the MMULT solution because we had earlier no way to expand the =counta(B2:G2) formula in each row.

But we can now use BYROW to expand the above Counta formula to expand count results in Google Sheets.

Insert this BYROW solution in cell H2, and voila!

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

We can include a logical test to skip blank rows, similar to our MMULT.

But here, it will be within the LAMBDA as follows.

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

If you have any doubts about using the above formulas, please write to me in the comments. Enjoy!

Resources:

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing 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.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

17 COMMENTS

  1. Hello,

    Thank you for your help! I have a table with arrays that create details by row, and I want to count them. However, the count is simply seeing a value in every cell, so 5 is returned for all rows.

    This is the formula I am using:

    =BYROW(AY6:BC,LAMBDA(ROW,IF(COUNTA(row)=0,,COUNTA(row))))

    The range AY:BC usually has an expected count of 1 or 2 for each row, so I know that 5 is wrong.

    • Hi Charles Jackson,

      I think you are using formulas in the range that returns an empty string, such as "". You can replace "" with IF(,,) in those formulas.

      For example, you can replace the formula =IF(1=1,"",2) with the formula =IF(1=1,IF(,,),2).

      The IF(,,) function will return a null string.

      • Yes, I managed to figure that out later on and switched to a COUNTIF, although determining the correct point in my nesting was trial and error. lol

        Many thanks Prashanth!

  2. I have a spreadsheet with multiple rows, causing it to need an arrayformula for calculation, and I’m trying to take a count of the columns with the value of 5 for each row (separately).

    I am confused on how do I change your original formula to only count the ones with a value of 5.

    I keep getting formula errors.

    • Hi, Tyrtle,
      In the above tutorial, find the side note just above the sub-title “Expanding Count Results in Google Sheets Using MMULT.”
      In that tutorial, you can find a possible solution (sample sheet included in that tutorial)

  3. Hi Prashanth,

    I find your tutorials quite useful. Thanks.

    I was looking for counting the occurrence of substrings in an array formula which brought me to this page. I did test your approach and work quite well. however, the problem I have is slightly different from the example you have on this page.

    Basically, I have rows that include employee name, start & end dates, variable ranges of weekdays (from Monday through Sunday), and weekly work hours. I want to create a matrix that displays the 7 days of the week, daily hours for each day with a “None” for the missing days; please see below for an example:

    StartDate|EndDate|WeeklyHours|Weekdays
    02/05/20|30/05/20|16|Monday, Friday
    03/03/20|31/03/20|28|Monday, Wednesday, Friday, Sunday
    02/05/20|29/05/20|18|Monday, Thursday, Friday

    I would like to add a column to the right calculating the average daily hours for each row. In the above case, it will be:

    StartDate|EndDate|WeeklyHours|Weekdays|DailyHours
    02/05/20|30/05/20|16|Monday, Friday|8
    03/03/20|31/03/20|28|Monday, Wednesday, Friday, Sunday|7
    02/05/20|29/05/20|18|Monday, Thursday, Friday|6

    I did try your approach, step by step, and using the SPLIT function too (again, using your tutorial):

    To create Matrix1, I used the following formula, which worked well:

    =iferror(ArrayFormula(len(iferror(SPLIT(D2:D,",")))/
    len(iferror(SPLIT(D2:D,",")))),0)

    To create Matrix2, I used the following formula, which failed with the error “Argument must be a range”:

    ArrayFormula(Transpose(column(iferror(SPLIT(D2:D,","))))^0)

    I guess this fails because it is expecting physical columns, generated from Matrix1 calculation.

    Any thoughts?

    Your reply will be much appreciated.

    Regards

    • Hi, Sabba,

      For the range A1:D and the row A1:D1 contains headers, then use following MMULT in E1.

      ={"Average";
      ArrayFormula(if(len(A2:A),C2:C/mmult(if(iferror(split(D2:D,","))<>"",1,0),
      transpose(sequence(1,columns(if(split(D2:D,",")<>"",1,0)))^0)),))}

      This would return the average as per your requirement.

  4. Hi Prashanth,

    I appreciated what you do!

    for sure will come here again for your best guide.

    Wish you a great ilfe!

    Thank You.

  5. This is so great!

    Really appreciated, thank you Prashanth

    For the best result, please help with the result from the formula in C2:D to be blank if equal or less then 0.

  6. MMULT Formula to Count Values in Each Row.

    Hi, how if I want to count values >0 in each row? please help.

    Thank You.

LEAVE A REPLY

Please enter your comment!
Please enter your name here