Filter in excel sheet using a formula with respect to multiple criterias

Question

I have a problem with my excel spreadsheet. I have hourly timeseries data of a variable (lets call it X) for different geographical areas, and the coherent temperature for each point in time. I would like to create a simple sheet filtering for multiple different criteria - these are:

• Geographical Area (4 different areas in the dataset. The area represents where the X variable and the temperature is measured)
• Year
• Week number
• Week day
• Hour
• Temperature

I would like to be able to have multiple selection of Area, Year, Weeknumber, Weekday, Hour and to be able to select a temperature range.

For instance, selecting:

Area: 2 (out of 4)

Year: 2019, 2020

Week number: 2,3,4

Week day: Monday, Tuesday, Wednesday

Hour: 5 pm, 6pm, 7pm

Temperature range: -5 degrees to +5 degrees

Ideally, my output would then be an array of my X variable filtered on these selections.

Currently my formula looks like this:

=FILTER('BackEnd (Area)'!B5:K36484;(('BackEnd (Area)'!J5:J36484<T5)('BackEnd (Area)'!J5:J36484>U5)) ISNUMBER(MATCH('BackEnd (Area)'!B5:B36484;'BackEnd (Time period)'!M3:M54;0))* ISNUMBER(MATCH('BackEnd (Area)'!D5:D36484;'BackEnd (Time period)'!K3:K54;0))* ISNUMBER(MATCH('BackEnd (Area)'!C5:C36484;'BackEnd (Time period)'!L3:L54))*ISNUMBER(MATCH('BackEnd (Area)'!F5:F36484;'BackEnd (Time period)'!J3:J54));1)

However, the filter formula does not take all of my selections into account. Please, can someone help me with this problem?

Thanks a lot!

Answer 1

Without recreating the data, try this:

=FILTER('BackEnd (Area)'!B5:K36484;(('BackEnd (Area)'!J5:J36484<T5)*('BackEnd (Area)'!J5:J36484>U5))*ISNUMBER(MATCH('BackEnd (Area)'!B5:B36484;'BackEnd (Time period)'!M3:M54;0))* ISNUMBER(MATCH('BackEnd (Area)'!D5:D36484;'BackEnd (Time period)'!K3:K54;0))* ISNUMBER(MATCH('BackEnd (Area)'!C5:C36484;'BackEnd (Time period)'!L3:L54))*ISNUMBER(MATCH('BackEnd (Area)'!F5:F36484;'BackEnd (Time period)'!J3:J54));1)