Filter based on value in cell

[onthegreen03]

Hi All - I have a tab with countries down the side (rows) and multiple columns of information including a percentage to goal (column M). See screenshot below:

On a separate tab I want to return 3 groupings:

1. The rows and columns where the percentage is greater than or equal to 100%
2. The rows and columns where the percentage is between 75% and 99%
3. The rows and columns where the percentage is between 0% and 74%

Thanks for the help!

 

[Joe4]

You can use the new FILTER function in Excel 365 to do this(it uses the Spill feature, so you only need to enter one formula, and it will return all rows meeting your criteria).

Here is an example that shows how to return the rows between two dates. But you can use this same logic to return the records between any numeric values (like your percentages):

Filter data between dates

To filter data to extract records between two dates, you can use the FILTER function with Boolean logic. In the example shown, the formula in F8 is: =FILTER(B5:D16,(C5:C16>=F5)*(C5:C16

exceljet.net

 

[onthegreen03]

Simple enough, thanks for the tip!

 

[Joe4]

You are welcome!
Yeah, the new FILTER function sure is handy!

 

[Bosquedeguate]

Have you considered using custom AutoFilters and creating a table?

 

[Joe4]

Have you considered using custom AutoFilters and creating a table?

How is that easier than using the new FILTER function?
It is literally one formula per sheet, and is totally dynamic.

I see that it appears that you are still using Excel 2016, so maybe you are not familiar with this new function, which does a lot of the same things setting up Filters does, but is actually much easier to use.