CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulascounting
Alex Libengood
Alex Libengood

Reputation: 553

Google Sheets Array formula for counting the number of values in each column

I'm trying to create an array formula to auto-populate the total count of values for each column as columns are added.

I've tried doing this using a combination of count and indirect, as well as tried my hand at query, but I can't seem to get it to show unique value counts for each column.

This is my first time attempting to use query, and at first it seemed possible from reading through the documentation on the query language, but I haven't been able to figure it out.

Here's the shared document: https://docs.google.com/spreadsheets/d/15VwsL7uTsORLqBDrnT3VdwAWlXLh-JgoJVbz7wkoMAo/edit?usp=sharing

I know I can do this by writing a custom function in apps script, but I'd like to use the built-in functions if I can for performance reasons (there is going to be a lot of data), and I want quick refresh rates.

Upvotes: 1

Views: 484

Answers (2)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(B5:5="",,TRANSPOSE(MMULT(TRANSPOSE(N(B6:99<>"")), SIGN(ROW(B6:99))))))

0

Upvotes: 1

JPV
JPV

Reputation: 27302

In B3 try

=ArrayFormula(IF(LEN(B5:5), COUNTIF(IF(B6:21<>"", COLUMN(B6:21)), COLUMN(B6:21)),))

Upvotes: 1

Related Questions