0

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.

1 Answer 1

1

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 
)
2
  • I just learned that filter does not support wildcards :S. How would you optimize your answer so that I can do not just D2:D = "A" but also find "AA" or "BA" using D2:D = "*A*"?
    – Adam
    Mar 6, 2023 at 13:54
  • 1
    Replace D2:D = "A" with search("A", D2:D). Please ask only one question per post. Post a new question instead. Mar 6, 2023 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.