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.

Improve this question
0

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

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...
You can't use sum() for text values. Depends on result you are expecting.
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.
I think you can try =QUERY({INDEX(TEXT(B1:B5,"@")),B1:B5},"select count(Col1),sum(Col2)").
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...
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.