Google Sheets Query [duplicate]

Question

With reference to the question how to remove sum label from query, I have a similar situation.

Where would I put the LABEL sum(E) in this formula?

=if(($B2="weekday"), ifna(QUERY('Base Data'!$A:$G, "select sum(E) where B = '"&D$1&"' and G = '"&$A2&"'",0),"") , "")

Answer 1

Solution

=IF($B2="weekday", IFNA(QUERY('Base Data'!$A:$G, "SELECT sum(E) WHERE B = '"& D$1& "' AND G = '"& $A2& "' LABEL sum(E) ''", 0)),)

Equivalent but line breaks added to assist visually:

=IF($B2="weekday", 
  IFNA(
    QUERY('Base Data'!$A:$G, 
      "SELECT sum(E) 
       WHERE B = '"& D$1& "' AND G = '"& $A2& "'
       LABEL sum(E) '' ", 0)),)

Explanation

1. The IFNA-wrapped QUERY function in your original formula is the IF function's value_if_true argument:

   =IF(logical_test, IFNA(QUERY), value_if_false)

2. QUERY follows function follows the syntax:

   =QUERY(data, query_text, [headers])

   Excluding its data argument, 'Base Data'!$A:$G, and headers, 0, what remains is the query_text:

   =QUERY('Base Data'!$A:$G, "SELECT sum(E) 
       WHERE B = '"& D$1& "' AND G = '"& $A2& "'", 0)
   

   and your LABEL sum(E) would go at the end of it

   "select sum(E) where B = '"& D$1& "' and G = '"& $A2&"' LABEL sum(E) '' "

Additional Notes:

1. In Sheets, IF function doesn't require a value if you want to return a blank, just include the comma without a value:
   

   =IF(1=2, valu_if_true, )

2. IFNA doesn't require a value or a comma if you want to return a blank:
   

   =IFNA(#N/A)