Count Multiple Columns and Get the Count Separately in Google Sheets

Published on

This post is not about the conditional count. In this tutorial, you can get a flexible array formula to count multiple columns and get the count separately. This is a Google Sheets tutorial.

I mean a single formula that counts across multiple columns and gets the count separately or you can say column-wise.

For example, you want to get the count of Column A, Column B, and Colum C separately. You may use three separate COUNT/COUNTA formulas to get this. Also, you can use a single Query too. But what about counting 10+ columns like that?

It’s of course possible. But it’s time taking and might be error-prone. Also when you want to modify the formula to include additional columns or change the column range, you may find it tough to deal with.

Here comes the importance of this tutorial. I have a formula that you can use to count n number of columns and get the count individually. Yes! You heard me right. I have the formula to do this and that is MMULT based.

The values in Columns can be number, text string, date or even tick boxes. The formula will work well with any type of values with some minor adjustments.

You May Also Like: 10 Best Tick Box Tips and Tricks in Google Sheets

Here is one example screenshot to show you how to count across columns using an array formula and get the count individually.

Once you understand what I am trying to explain to you, you can go to the formula section below. In this example, my array formula is in cell B1 (which is not visible though).

Count Multiple Columns and Get the Count Separately

How to Count Multiple Columns and Get the Count Separately

This is not a step by step tutorial. I will give you the formula straightaway and guide you how to use the formula for different types of values and column ranges.

In the above example, there are 5 columns to count individually. My intention is to count the total number of features of each product (product 1, product 2 etc.) individually.

The count range is B3: F. Also here what I want to count in each column is the Boolean TRUE/FALSE. Why?

This is because the tick box value is the Boolean TRUE when it’s ticked. Otherwise, the value of the cells wherever the tick boxes are placed are the Boolean FALSE.

So with my formula in B2 I am counting the Boolean TRUE in each column.

Formula:

=ArrayFormula(TRANSPOSE(mmult(TRANSPOSE(if(B3:F=TRUE,1,0)),row(indirect("A1:A"&columns(TRANSPOSE(if(B3:F=TRUE,1,0)))))^0)))

I have more columns to count. How to modify this formula?

Simply change the range B3: F which is appearing twice in the formula.

Change both to the range that you want to include in the count. I want to count across A1: J. So what should I do? Just replace B3: F with A1: J. That’s all that you want to do.

Don’t forget to place the formula outside the above range to avoid circular dependency error.

How to count multiple columns with the above array formula when the values in the columns are numbers.

Array Formula to Count Multiple Number Columns

You can use the same above formula with minor changes. The changes I’ve underlined in the below screenshot.

count across columns and get the output in array

The formula only counts the values above 0. It counts numbers in each column and returns the count separately.

Here is one more example that shows how to count multiple columns and get individual column wise count. This time let me use the above formula in a text column.

Array Formula to Count Multiple Text Columns

This example you can use to count the presents and absent of your employees if it’s entered in column wise.

Here again, I’ve highlighted/underlined the changes in the formula.

multiple column count and individual result

Conclusion

When you want to count the values in an unlimited number of columns and get the count results column-wise, don’t look any further. Use my formula.

The above formula is the ultimate one to count n columns and return column wise count in Google Sheets.

This is a rare piece of formula using MMULT. The other functions in this formula are TRANSPOSE, ROW, INDIRECT etc. To learn all these functions do visit my Google Sheets Function Guide. Thanks for the stay. Enjoy!

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.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

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...

1 COMMENT

  1. Well, thx man.

    I’ve spent the last 2 hours on the web searching for this.
    I’ve used the MMULT before for a somewhat similar purpose – summing up the numeric values for each column of the table separately, but couldn’t figure out how to do the count of values – especially the text values, which I need in my new task.

    Imma checks your function tutorial at some point.

    Thank you very much for your selfless contribution…

    Cheers

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.