I'm trying to get the count of non-empty cells in a column of mixed numbers and text in a Google Sheets document using the QUERY function, but I'm getting some results I don't understand from the count aggregate function inside the query.
I've reduced my issue to this example to demonstrate the problem (or at least show where my misunderstanding is):
| . | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | 10 | 10 | 10 | 10 | 10 | aa |
| 2 | 20 | 20 | 20 | 20 | bb | bb |
| 3 | 30 | 30 | 30 | cc | cc | cc |
| 4 | 40 | 40 | dd | dd | dd | dd |
| 5 | 50 | ee | ee | ee | ee | ee |
| 6 | ||||||
| 7 | select count(A) | select count(B) | select count(C) | select count(D) | select count(E) | select count(F) |
| 8 | =query(A1:A5,A7) |
=query(B1:B5,B7) |
=query(C1:C5,C7) |
=query(D1:D5,D7) |
=query(E1:E5,E7) |
=query(F1:F5,F7) |
| 9 | 5 | 4 | 3 | 5 | 5 | 5 |
This gives me the expected value in cells A9, D9, E9 and F9, but I've got a couple of questions:
- what is going on in columns B and C to cause them to give counts of 4 and 3 respectively?
- how do I modify my function / query to count the number of non-empty cells in the query range regardless of data types?
I know there are other functions like COUNTA that could solve this simple case, but my actual scenario is more complicated and is part of a larger QUERY function, and it's highlighted to me that the behaviour of query("... count() ...") isn't what I expected, so I'm trying to understand that specifically.
CodePudding user response：
Try below formula-
=QUERY(INDEX(TEXT(B1:B5,"@")),"select count(Col1)")
QUERY() function is auto detecting these columns as numbers so it is excluding text strings.