0

On Sheet 1, I have data

CATEGORY  |  PATIENT  |    DATE    |  AMOUNT
Vision    |  Joe      |  1/1/2016  |   $100
Vision    |  Joe      |  1/2/2016  |   $100
Vision    |  Joe      |  1/3/2016  |   $100  

On Sheet 2,

CATEGORY  |  PATIENT  |  TOTAL 2016  |
Vision    |  Joe      |     $300     |

However I don't have this working. The query I am using in Column C of Sheet 2 is:

=QUERY('Sheet'!1:1000, "SELECT SUM(D) WHERE A='"& A2 &"' AND B='"& B2 &"' AND '"&YEAR(C)&"'='2016' label SUM(D) ''")

However I am getting an error

#NAME?
Unknown range name: 'C'.

Why can't I pass the column C to the YEAR function?

2
  • Because Year needs a cell (ie: C1) as an argument, not a column (ie: just C). anyhow, questions about Google-Sheets are off-topic for SU - voted to migrate to WebApps.
    – techie007
    Jan 24, 2017 at 18:32
  • Oh thanks. So is there some way to use this function in a Query? Jan 24, 2017 at 18:57

2 Answers 2

1

Because you are using the wrong syntax. Guessing that you want a query to populate a single cell (assumed to be C2), please try:

  =QUERY(Sheet1!A1:D1000,"SELECT sum(D) WHERE A='"&A2&"' AND B='"&B2&"' and C >= date '2016-01-01' AND C <= date '2016-12-31' label SUM(D) ''") 
0

I'm assuming the OP didn't just want the sum for the date range solely for "Joe." It seems what is called for here is running a QUERY that first filters the data for only those entries within the date range, and then running a QUERY on that initial QUERY using "GROUP BY":

=QUERY(QUERY(A:D,"Select A, B, D WHERE C >= date '2016-01-01' and C <= date '2016-12-31' and NOT D = ''"),"Select Col1, Col2, SUM(Col3) GROUP BY Col1, Col2")

This will produce a new chart with headers, showing totals for each person by category, drawn only from the desired date range. The sums for the date range make having a "Date" column unhelpful, so that won't be part of the newly created chart, just "Category | Patient | Sum Amount."

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.