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

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.

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.

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!