1

Tab Data is as under

Date_A       Project_ID
01-May-18   EL-PR-18-08709-1
02-May-18   EL-PR-18-08709-1
01-May-18   EL-PR-18-08709-1
03-May-18   EL-PR-18-08709-1
02-May-18   EL-PR-18-08709-1
01-May-18   EL-PR-18-08709-3
04-May-18   EL-PR-18-08709-3
03-May-18   EL-PR-18-08709-3
05-May-18   EL-PR-18-08709-3
05-May-18   EL-PR-18-08709-3
30-Apr-18   EL-PR-18-08709-2
01-May-18   EL-PR-18-08709-2
02-May-18   EL-PR-18-08709-2

I want to Query Max(Date_A) based on a particular Project ID and want to place it in Tab Main, I have tried =Query(Data!A1:B12,"Select max(A) where B='EL-PR-18-08709-1'",1) but it gives Parse error.

I understand that Max cannot be used with where clause.

  • Maybe the cause of the problem is the regional settings of your spreadsheet causing that you should use ; as parameter separator instead of ,. – Rubén May 7 '18 at 20:57
  • 2
    For me, the query formula is working as well. It's perfectly possible to use MAX and WHERE clauses together in a QUERY(). Maybe share your spreadsheet so that we can debug the problem further. – ttarchala May 8 '18 at 7:59
0

Thanks for your comment, Ruben. Actually , as a parameter separator is working nicely on my computer, so that is not the issue. Query function does not permit to use Max and where together in a query. Anyhow, I could find another way to extract the Max date as per my criteria by the following formula ;

=ARRAY_CONSTRAIN(SORT(
 filter('Data'!$A$2:$A,'Main'!$B$2:$B='EL-PR-18-08709-1'),1,FALSE),1,1)

I am writing this formula in Tab Main in the cell where I want this Max date.

| improve this answer | |
  • 1
    "Query function does not permit to use Max and where together in a query." Not true. It must be something else. – TheMaster May 9 '18 at 14:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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