Query syntax in Google Spreadsheet

I was calculating my expenses related to medical for the purpose of Tax report. All my data is recorded in a spreadsheet in Google Spreadsheet. After experimenting, I am now pleased with its formula, especially the power of query syntax (similar to SQL syntax).


Assume a spreadsheet named "2010" where it contains tax-deductible data. Row 1 cells contain title. Actual data starts from row 2 to row 419 (range is A2 to J419)


Cells in column G contains expenses related to medical. Each Cell in column J contain text, where for medical-related expense it should contain "medical" etc.


Now, to calculate the sum of all medical-related expenses, we can put the following formula somewhere in empty cell down below as:


=query('2010'!$G$2:$J$419;"select sum(G) where J contains 'edical' "; 0)


contains query is not exact-matching, so the logic is still TRUE even if we have "medical" or "Medical".


The reason I omit "m" in "medical" is to avoid case-sensitive query (I might put "Medical" instead of "medical" in my data). We can also put LOWER() function for J to force case-insensitive matching so it will still match any case of the letters, for example:

=query('2010'!$G$2:$J$419;"select sum(G) where LOWER(J) contains 'medical' "; 0)

In this case, I put the formula in another spreadsheet (that's why you see prefix '2010' there to refer to spreadsheet named "2010").


To do logical NOT, the syntax is "NOT J contains 'edical". If you want to do logical AND, put AND in front of NOT, so it will be: "NOT J contains 'medical' AND NOT J contains 'dental' "

The best so far is as below:


=ArrayFormula(IFERROR(query('2010'!$G$2:$J$419;"select sum(G) where LOWER(J) contains 'medical'"), 0))



and the next row will fill with:


=iferror(CONTINUE(B2, 2, 1))



There are many more formulas we can experiment: MATCH(), FILTER() and so on.