I have two sheets of data, the second sheet is "DATA".
The primary sheet I'm using, I have a =query() to query the other sheet based on a month number. From my research, I found this - https://www.benlcollins.com/spreadsheets/query-dates/ - which by itself, does work, but within query(), doesn't work.
Data:
1/1/18 2/1/18 3/1/18 4/1/18
Broken function:
=IFERROR(QUERY(DATA!$A:$L, "Select sum(G) WHERE B contains lower('"&$A4&"') AND MONTH(A)+1='"&TEXT(DATEVALUE(C1),"m")&"' label sum(G) ''"),"$0")
Working function:
=IFERROR(QUERY(DATA!$A:$L, "Select sum(G) WHERE B contains lower('"&$A4&"') AND MONTH(A)+1=4 label sum(G) ''"),"$0")
Problem...is that I want it to be dynamic.
When I use
'"&TEXT(DATEVALUE("1/1/2000"),"yyyy-mm-dd")&"'
as a function, it renders as a "4" which is what the query() is looking for.
What about something like
=IFERROR(QUERY(DATA!$A:$L, "Select sum(G) WHERE B contains lower('"&$A4&"') AND MONTH(A)+1= "&MONTH(C1)&" label sum(G) ''"),"$0")