Filtering based on a range of number

[JmccoyPeerless]

I have a gallery I am using to display recorded incidents.  On the page I have three drop downs: Department, Amount, and Status. These are being used to filter the Gallery. The Gallery is made up of a SQL table that is populated from the app and an excel List. I combined the two with collect and lookup. In the Amount drop down I have 1-5 sticks, 5-10 sticks, 10+ sticks, and All. this was working just fine in the filter below because the table with amount also had the above values for the column, 1-5 Sticks, 5-10, 10+. However, now we are changing the dropdown that populates the SQL table to in lieu of having the ranges it will now allow the user to pick an exact number from 1-100 pieces. So this column will now only have single values in lieu of a range. My question is how do I edit the below formula to filter the gallery based on amount still using the ranges. My goal would to leave in the ranges in the drop down being used for a filter. Then in the filter I have it look between those ranges and determine what rows meet the criteria.

When amount between 1-5 = "1-5 Sticks"

Filter(TagCollection,
Amount = AmountDropDown.Selected.Amount || AmountDropDown.Selected.Amount = "All",
Status = StatusDropdown.Selected.Status || StatusDropdown.Selected.Status = "-",
SuspectedDept = DepartmentDropDown.Selected.Result || DepartmentDropDown.Selected.Result = "All")

[mdevaney · Accepted solution]

@JmccoyPeerless
Hello there!

What I understand is: you have a dropdown (AmountDropdown) having the values All, 1-5 Sticks, 5-10 Sticks, 10+ Sticks.  When you select 1-5 Sticks you want only the records to show having between 1 and 5 sticks.

Here's my solution.  I assume TagCollection has a column name Amount.

Filter(
    TagCollection,
    AmountDropDown.Selected.Amount = "All"
    Or AmountDropdown.Selected.Amount = "1-5 Sticks" And Amount >= 1 And Amount <= 5
    Or AmountDropdown.Selected.Amount = "5-10 Sticks" And Amount >= 5 And Amount <= 10
    Or AmountDropdown.Selected.Amount = "10+ Sticks" And Amount > 10,
    Status = StatusDropdown.Selected.Status || StatusDropdown.Selected.Status = "-",
    SuspectedDept = DepartmentDropDown.Selected.Result || DepartmentDropDown.Selected.Result = "All"
)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[olasprilla01]

Please help, I was not able to display lessthan zero for this syntax