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.