Google Sheets: Query Function - Counting unique values, but failing to show zeros-CodePudding

I am trying to create a function that allows me to count how many times a column has a certain phrase (i used dropdown menus in the column) and then sorting it by the month. However, if there is a month that doesn't have any of the items I am searching it doesn't populate a row in the query table showing that month had zero of the burgers, etc. Does anyone know how to have the query function show the 0 for months that did not have results.

=QUERY('2022'!A1:Z, "select count(E) where E= 'Burgers' and B >=date """&text(D2,"yyyy-MM-dd")&""" and B<=date """&text(D3,"yyyy-MM-dd")&""" group by B Label count(E) 'Burgers'")

CodePudding user response：

Use the following formula in a single cell.

={INDEX(TEXT(DATE(YEAR(D3),SEQUENCE(MONTH(D3)-MONTH(D2) 1),1),"mmmm yyyy")),
INDEX(MAP(DATE(YEAR(D3),SEQUENCE(MONTH(D3)-MONTH(D2) 1),1),LAMBDA(x,COUNTIFS(Data!D2:D,"Burgers",Data!A2:A,">="&x,Data!A2:A,"<="&EOMONTH(x,0))))),
INDEX(MAP(DATE(YEAR(D3),SEQUENCE(MONTH(D3)-MONTH(D2) 1),1),LAMBDA(x,COUNTIFS(Data!D2:D,"Drinks",Data!A2:A,">="&x,Data!A2:A,"<="&EOMONTH(x,0)))))}