Converted Filter function into Query

Question

I have created filter function to Filter some sort of data and it is working fine but later converted it to Query but i am stcuk on one thing that how to Filter the Date Column based on month values like (1,2,3,4,5,6,7,8,9,10,11,12). I have created a Data validation for all 12 months.

your help will be much appreciated.

Filter Function:

=IFERROR(FILTER('Sheet1'!B:K,'Sheet1'!D:D=M4,month('Sheet1'!E:E)=X4),"No Data Available")

Query Function:

=QUERY({'Sheet1'!B:K}, "SELECT * WHERE Col2 contains '"&J4&"' AND Col3 contains '"&M4&"' AND Col4 = month(date'"&text(X4,"M")&"') ORDER BY Col2 ASC",0)

Unable to make this Query line AND Col4 = month(date'"&text(X4,"M")&"')

z.. · Accepted Answer · 2023-01-13 14:03:11Z

1

Try:

=QUERY({'Sheet1'!B:K}, "SELECT * WHERE Col2 contains '"&J4&"' AND Col3 contains '"&M4&"' AND month(Col4) ="&X4-1&" ORDER BY Col2 ASC",0)

answered Jan 13, 2023 at 14:03

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

player0 · Accepted Answer · 2023-01-13 15:04:59Z

1

try:

=QUERY({Sheet1!B:K}, 
 "where Col2 contains '"&J4&"' 
    and Col3 contains '"&M4&"' 
    and month(Col4)+1 ="&X4&" 
  order by Col2", )

you can skip these:

select * if you want to get all columns you can just skip select argument
single quotes are needed only if sheet name contains a space
months in query start from 0 (as January) so we just add +1
when ordering its asc by default so we can skip it
zero 0 as the 3rd argument can be omitted

answered Jan 13, 2023 at 15:04

player0

131k14 gold badges92 silver badges149 bronze badges

Collectives™ on Stack Overflow

Converted Filter function into Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related