Google-sheets – Count blank cells in query formula

formulasgoogle sheetsgoogle-sheets-query

I am ranking answer for a form. and I came up with this formula

=ArrayFormula(QUERY({'LEADs oGV'!U:U,LEN('LEADs oGV'!U:U)},
 "select Col1, count(Col2) where Col2 > 0 group by Col1 order by count(Col2) desc limit 3",
 0))

The problem is that it does not count the number of blank answers in the given column! How can I do that without filling every single blank cell?

Best Answer

Maybe try:

=ArrayFormula(QUERY({'LEADs oGV'!U:U,LEN('LEADs oGV'!U:U)},
 "select Col1, count(Col2) group by Col1 order by count(Col2) desc limit 4",0))

Related Solutions

Google Sheets Queries – Using Named Ranges in Google Sheets Queries

It's a kind of tricky, but it is possible with a helper Range and some concatenation.

What needs to be done:

Create a named range, COLS, to carry the column letters like this:
```
A  
B  
C  
D  
E  
...
```
Do it in a vertical way as shown.

Assemble the query string like this:

=QUERY( B:J, "SELECT AVG(" & INDEX(**COLS**, COLUMN(J1)) & ") GROUP BY " & INDEX(**COLS**, COLUMN(G1)) )

Google-sheets – Grouping Multiple Data to Output Common Values in Google Sheets

The data summary that you are trying to build, some people call them "crosstab report" or "pivot table".

Google Sheets, have at least two easier than the complex formula in the question ways:

QUERY function using the GROUP BY and PIVOT keywords. See QUERY for details.
Pivot Table report. See How to create a pivot table report for details.

Best Answer

Related Solutions

Google Sheets Queries – Using Named Ranges in Google Sheets Queries

Google-sheets – Grouping Multiple Data to Output Common Values in Google Sheets

Related Topic