Google Sheets – Dates Treated as Text Even with TO_DATE()

If you add the following formula in the second sheet (A1).

Formula

=QUERY(Sheet1!A1:C9;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A PIVOT C")

then the table will appear as you want.

Explained

Here an explanation of the formula, from the inside to the outside:

• the PIVOT C transposes the unique results from column C (turning rows into columns)
• the GROUP BY A only filters out unique values in column A. (the same as sheet TABLE!A2)
• the WHERE B IS NOT NULL ignores empty cells
• the SUM(B) adds the result of the query
• the SELECT A simply displays column A, as unique values

If you're only interested in individual results, then I suggest using the following formula:

=IFERROR(SUM(FILTER(DATA!$B$2:B;DATA!$C$2:C=$B$1;DATA!$A$2:A=A2));"")

Here an explanation of the formula, from the inside to the outside:

• the FILTER function will retrieve the amounts for range B2:B, column B without the header, by filtering range B2:B for "No" and range C2:C for "Yes".
• the SUM function will add them together.
• the IFERROR will leave a blank cell if an error occurred (not result).

Screenshot

Example

See example file I've prepared, where both samples are present: SUM data based on two variables

Note

The formula in the example file is a bit different (the range is set to contain complete columns).

The method setNumberFormat does the job:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange("C:C");
  range.setNumberFormat("yyyy/MM/dd");
}

This formats column C as yyyy/MM/dd. Many other date formats are available: the method setNumberFormat supports Java's SimpleDateFormat.