I have a spreadsheet (https://docs.google.com/spreadsheets/d/1R0zYMAigDULzm8oM2icsPK55BP162kVvctpx2cOSOIE/edit?usp=sharing) where I am entering Groceries on each row every time I visit a store.
I have created a query to output the sum for each store for all rows. This works great.
=QUERY(A9:C, "select C, SUM(B) where B is not null GROUP BY C LABEL C '', SUM(B) ''")
Now I would like to use pretty much the same query, but I would like to limit it to only sum the entries that are from the past six months. This is my query:
=QUERY(A9:C, "select C, SUM(B) where date '2016-08-28' < A GROUP BY C LABEL C '', SUM(B) ''")
This works great as well, except I would like to have it list ALL the stores and if there are no entries for a store (such as Bristol Farms and Safeway in the example), then instead of omitting it I would like for it to display as 0.
So I would like for it to display like this:
How can I do that?
If you don't mind an extra column (you could hide it), on you spreadsheet, put this in J9:
And this in K11:
=QUERY(A9:J13,"select C, sum(J) group by C order by C label sum(J)''")
I couldn't get Max's answer to work with sum. Maybe you can.