Google Sheets Query returning results that don't exist in the source list

Question

I am creating a budget tracker within Google Sheets. I am having trouble with Google Query Language returning results that do not exist in the source data

I have a Google Form into which expenditure can be entered, which populates an associated Sheet in my Google Drive. My Master budget tracker also sits within my Google Drive. I use IMPORTRANGE to pull the data into the Master from the Responses spreadsheet, and then Query to separate this out into different 'expenditure' categories on sheets within the workbook (one for each month). February worked perfectly - all expenditure was found and summed correctly and then the February sheet was duplicated for each month of the year, the formulae or look up terms updated. But March is acting strangely - it is returning a value for March that doesn't exist - and not only does the summed value not exist but the word 'March' also does not exist either, so shouldn't be matching. I have tweaked the code, tried refreshing and rewriting the formula into the cell to force a refresh, re-imported the range from the external spreadsheet, I have tried various parentheses placements (I'm not a SQL or Google Query Language expert, so am feeling my way a bit) as I thought it was something to do with the AND/OR clauses not being 'collated', but none has produced even a different result, it's always the same false value being returned

The query code is as follows:

=query(spend, "select sum(B) where H = '"&$H$1&"' and E='Leisure' or E='Tickets' or E='Parking' or E='Other' label sum(B) ''", -1)

'spend' is the range containing the data imported from the Responses spreadsheet, which includes several post form completion formulae coding the row with a day and a month. Right now, there are only values coded as 'February' in H - nothing else. Cell H1 contains the month name (written in, not formula). This formula works perfectly within the 'February' sheet, and if I update cell H1 to read 'February' in the March sheet it shows the accurate values for February, however, if I enter 'March' in H1 I am getting the odd outcome

I am expecting a £0 result for March, but instead, I am getting a value of £19.46. As previously described - the source list 'spend' only contains values coded as February in H, and the value £19.46 does not appear singularly in the list (and doesn't appear to be made by any values when 'summed'). I am at a loss as to what is happening, and no answers seem to address the appearance of mystery values, so I hope I'm not repeating old ground - please do correct me if I am, and many thanks in advance for any guidance

Will do, it contains sensitive data so will just change some of the numbers and names. Bear with me, my apologies — Madraykin
Hi there, my apologies for the length of time taken - been a busy day! Hopefully you should be able to access this - the structure is the same, but I've redacted any sensitive information - let me know if this is ok. As I said, I'm still learning so I'm sure I'm missing something really obvious, but I'll be darned if I can see it. — Madraykin

player0 player0 · Accepted Answer · 2019-02-19T20:24:35

you picked 19.46 because even if H1 wasn't found in Query, Query continued to evaluate for AND and OR statements and sum up a bunch of nonsense

=IFERROR(IF(QUERY(spend, 
              "select count(H) 
               where H='"&$H$1&"' 
               label count(H)''", 0)>0,
 QUERY(spend, "select sum(B) 
               where (H='"&$H$1&"') 
                 AND (E='Leisure') 
                  OR (E='Tickets') 
                  OR (E='Parking') 
                  OR (E='Other') 
               label sum(B)''", -1), ), )

Google Sheets Query returning results that don't exist in the source list

2 Answers