filter, sortby then exclude specific rows that is included in the filter criteria

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
308
Office Version
  1. 365
Platform
  1. Windows
Would it be possible to exclude rows from a filtered list in which that said rows are included from the filtered criteria
i have a current formula that will filter >5 # of days (courtesy of sir @Fluff ). However, i have a separate report that i need to exclude 1 name (Welles) from the list.

testing.xlsx
ABCDEFGHIJKLMNOPQRST
1Nameno.sectiondeptqtyCurrent formulaexpected result
2Lara1ZAZ5SORTBYNameno.dept# of days (updated everyday)Nameno.dept# of days (updated everyday)
3Gayle2ABA10ZGayle2A10Gayle2A10
4Ponting3CDCAKipling7X6Kipling7X6
5Welles4DED20XConnery10B32Connery10B32
6Perot5BCB5BWelles4D20Porter9D12
7Murdoch6EFE4CPorter9D12
8Kipling7XYX6D
9Coolidge8TUTT
10Porter9DED12E
11Connery10BCB32
Sheet11
Cell Formulas
RangeFormula
I3:L7I3=LET(f,FILTER(CHOOSECOLS(A2:E100,1,2,4,5),E2:E100>5),SORTBY(f,MATCH(INDEX(f,,3),G3:G10,0)))
Dynamic array formulas.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Maybe:
Excel Formula:
=LET(f,FILTER(CHOOSECOLS(A2:E100,1,2,4,5),(E2:E100>5)*(A2:A100<>"Welles")),SORTBY(f,MATCH(INDEX(f,,3),G3:G10,0)))
 
Upvote 0
Solution

Forum statistics

Threads
1,216,783
Messages
6,132,690
Members
449,748
Latest member
freestuffman

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
Back
Top