Google Sheets Query – Use COUNT Without Expanding Array

google sheetsgoogle-sheets-query

My goal is to dedupe results based on column B, filter by last X days in column 13 (omitted in sample data I can work that part out myself), and then count the number of rows that remain with a value of "A", and show that number in a single cell (not expand the array).

My data:

A       B           C   D   E
1       ffhh548     x   A   1000
2       ffhh548     x   A   1000
3       gd64932     x   B   65
4       056gjf9     x   A   4566
5       gfkl47p     x   C   649
6       4850256     x   U   534
7       vnflkjg     x   F   8574
8       99gfklf     x   A   45

My desired outcome would be: 3 (rows 1, 4, 8).

This works to sum all values in column 5:

=query( 
  sortn(A2:M, 9^9, 2, B2:B, true), 
  "select sum(Col5) 
   where Col4 = 'A' 
   and Col13 > date " & text(edate(today(), -$C$1), "'yyyy-MM-dd'") & " 
   label sum(Col5) '' ", 
   0 
)

However, when I alter it so simply count the number of occurences of "A", my query fails.

=query( 
  sortn(A2:Q, 9^9, 2, B2:B, true), 
  "select COUNT(Col2) 
   where Col4 = 'A' 
   and Col13 > date " & text(edate(today(), -$C$1), "'yyyy-MM-dd'") & " 
   label sum(Col4) '' ", 
   0 
)

I get error:

Unable to parse query string for Function QUERY parameter 2: LABEL_COL_NOT_IN_SELECT: SUM(Col3)

I tried simplifying my query to just get a count, but that also fails:

=query( 
  sortn(A2:Q, 9^9, 2, B2:B, true), 
  "select COUNT(Col2) WHERE Col4='A'  
   label sum(Col4) '' ", 
   0 
)

I get error:

Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered "" at line 1, column 42. Was expecting one of: <STRING_LITERAL> … "" … "+" … "-" … "/" … "%" … "" … "/" … "%" … "+" … "-" …

=query( 
  sortn(RFX!A2:Q, 9^9, 2, RFX!B2:B, true), 
  "select COUNT(Col2) WHERE Col4='A' label SUM(Col4) ''", 
   0 
)

I get error:

Unable to parse query string for Function QUERY parameter 2: LABEL_COL_NOT_IN_SELECT: SUM(Col4)

I checked here https://stackoverflow.com/questions/44740466/google-spreadsheet-query-count and tried:

=ArrayFormula(query( 
  sortn(A2:Q, 9^9, 2, B2:B, true), 
  "select COUNT(Col2) WHERE Col4='A'", 
   0 
))

I get error:

Array result was not expanded because it would overwrite data in C11.

Best Answer

Use filter() and counta(), like this:

=counta( unique( iferror( filter(B2:B, D2:D = "A") ) ) )

If you need to use query() for some reason, try this:

=query( 
  sortn(A2:D, 9^9, 2, B2:B, true), 
  "select count(Col2) 
   where Col4 = 'A'  
   label count(Col2) '' ", 
  0 
)
Related Topic