Trouble building multiple constraint filter in excel

macattack1

New Member
Joined
May 12, 2022
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
Hello everyone. Awesome forum! Hope everyone is having a wonderful Thursday. I was wondering if any of you could help me with this problem I have been having in excel. I am creating a filter function based off of raw data to give me data from that raw data given two date constraints and two dollar constraints. The syntax is as follows:


VBA Code:
=FILTER('Raw Data'!D7:M1000,('Raw Data'!D7:D1000>=D37)*


('Raw Data'!D7:D1000<=D38)*('Raw Data'!E7:E1000>=E37)*


('Raw Data'!E7:E1000>=E38)*('Raw Data'!F7:F1000>=F37)*


('Raw Data'!F7:F1000>=F38)*('Raw Data'!G7:G1000>=G37)*('Raw Data'!G7:G1000>=G38),"")





Where D7:M1000 is the raw data range and D, E, F, G are the columns from it I am attempting to pull from. The values at the end of each statement following ">=" and "<=" are the specific cells that are constraints. I believe this syntax is correct but unfortunately, it is not returning any values given the for ranged constraints. Is there something I am doing wrong? Is there something I need to add? Please let me know if you have any idea. I appreciate your consideration and thank you for any help you are able to provide.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,557
Office Version
  1. 365
Platform
  1. Windows
Why have you got to criteria for col G that are >=, shouldn't one of them be <= ?
 

macattack1

New Member
Joined
May 12, 2022
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
Thank you so much that worked. Great eye! Now I am trying to apply it to the other five metrics. Do you know if there is a way to embed an IF(FALSE statement not to worry about any of those constraints?

Here is the syntax I have:

=FILTER('Raw Data'!D7:N1000,('Raw Data'!D7:D1000>=F12)*

('Raw Data'!D7:D1000<=H12)*('Raw Data'!E7:E1000>=F14)*

('Raw Data'!E7:E1000<=H14)*('Raw Data'!F7:F1000>=F18)*

('Raw Data'!F7:F1000<=H18)*('Raw Data'!G7:G1000>=F20)*('Raw Data'!G7:G1000<=H20)*

(('Raw Data'!H7:H1000=F24) + ('Raw Data'!H7:H1000=H24) + ('Raw Data'!H7:H1000=J24))*

(('Raw Data'!I7:I1000=F26) + ('Raw Data'!I7:I1000=H26) + ('Raw Data'!I7:I1000=J26))*

(('Raw Data'!J7:J1000=F28) + ('Raw Data'!J7:J1000=H28) + ('Raw Data'!J7:J1000=J28))*

(('Raw Data'!K7:K1000=F30) + ('Raw Data'!K7:K1000=H30) + ('Raw Data'!K7:K1000=J30))*

(('Raw Data'!L7:L1000=F32) + ('Raw Data'!L7:L1000=H32) + ('Raw Data'!L7:L1000=J32)),"")



Applied to this string ==> "('Raw Data'!H7:H1000=F24)" and below? You have helped me so much. Thank you. I feel like I am very close. I tried doing this below:

(IF(E24="FALSE",(('Raw Data'!H7:H1000=F24) + ('Raw Data'!H7:H1000=H24) + ('Raw Data'!H7:H1000=J24)),""))

But excel gives me the value message. Let me know if you know if it is possible and thank you again for all of your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,557
Office Version
  1. 365
Platform
  1. Windows
What exactly is in E24?
 

macattack1

New Member
Joined
May 12, 2022
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
E24 is a TRUE/FALSE values. The way in google sheets it has to be done in a query, is it has to be written like this below:

("&E26&" = false OR (H = '"&F26&"' OR H = '"&H26&"' OR H = '"&J26&"'))

Where E26 is a checkbox making it true if checked and false if not. Any idea how to construct this in Excel?

Thanks for the help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,557
Office Version
  1. 365
Platform
  1. Windows
Ok try
Excel Formula:
(IF(E24=FALSE,(('Raw Data'!H7:H1000=F24) + ('Raw Data'!H7:H1000=H24) + ('Raw Data'!H7:H1000=J24)),1))
 

Forum statistics

Threads
1,181,057
Messages
5,927,875
Members
436,574
Latest member
Bosniain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top