0

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.

mclayton
  • 8,025
  • 2
  • 21
  • 26

1 Answers1

2

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.

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • Fab, thanks. That works in isolation, but it obviously changes the column's datatype so other functions like ```sum(Col1)``` don't work. I'm not sure there's actually a clean silver-bullet solution, tbh - I'll probably end up using your answer in a separate query and then combine it somehow with my existing query results on a new sheet... – mclayton Aug 26 '22 at 10:43
  • You can't use `sum()` for text values. Depends on result you are expecting. – Harun24hr Aug 26 '22 at 10:46
  • So I've got mixed numeric and text in a single column - I'm trying to get a count of all values, and a sum of the numbers. However, if ```QUERY``` detects the column as numeric per my original question it ignores text in the ```count``` and if I use this answer the ```sum``` fails, so I don't think I can do both in a single query. – mclayton Aug 26 '22 at 10:50
  • I think you can try `=QUERY({INDEX(TEXT(B1:B5,"@")),B1:B5},"select count(Col1),sum(Col2)")`. – Harun24hr Aug 26 '22 at 11:00
  • Ah, nice. Yeah, that works. I'll need to add some more columns to integrate it into my actual sheet, but that definitely solves the problem I was having. If you want to add that to your answer I'll accept that... – mclayton Aug 26 '22 at 11:13
  • You can accept the answer. Comments are also part of answer. – Harun24hr Aug 26 '22 at 14:13