In Google Query, return 0 to cell when query returns empty set for a particular month

Question

Using Google Query, I would like to return 0 to a cell when the query returns an empty set for a particular month in 2018.

This is the query I am using with the named range all_data:

=query(all_data,"SELECT SUM(A) WHERE F <> 'Converted' GROUP BY MONTH(N),1)

Column N contains a list of dates. The output I am getting is:

blank_cell
1
2
4
14

But what I would like to get based on the dataset, if blank months returned as 0, is

0
0
0
0
0
1
0
0
0
2
4
14

Note there are 12 values in my desired output, each corresponding to a particular month.

Once I figure out the above, my next challenge will be figuring out how to do this for 2 years worth of data (2017-2018).

So far I have

• Checked the Google Query documentation but couldn't figure it out there
• Tried using IFERROR - doesn't help because there is not technically an error occurring

My strong preference is to implement this using QUERY rather than VLOOKUP, SUMIF, or some other function because that is how the rest of the data in this analysis is being generated and consistency will help other users of the sheet.

Thanks for any ideas you have!

Answer 1

The easiest way may be to make sure your data set has at least one instance of every month without Converted in ColumnF and with 0s in ColumnA.