In F5, list all the deals (all 4 columns) from the region in F2 with an amount of at least G2: the conditions are joined by AND.
Lesson theory
An ordinary AutoFilter picks values from a dropdown list. The Advanced Filter ("Data › Sort & Filter › Advanced") takes its conditions from a separate criteria range on the sheet:
- the first row of the range holds exact copies of the table headers ("Region", "Amount"), and the conditions go below them;
- conditions in one row are joined by AND:
Westand>=400means West deals of $400 or more; - conditions in different rows are joined by OR: "West" in one row, and "Laptop" under "Product" in the next;
- for a range on a single column, repeat the header twice: "Amount >=400" and "Amount <=1000" in the same row;
- you can "Copy to another location" and check "Unique records only".
The drawback of the Advanced Filter is that it does not update itself: change a condition and you have to run it again. In Excel 365 a live formula does the same job:
=FILTER(A2:D11, (B2:B11=F2)*(D2:D11>=G2)) is AND: the two conditions are multiplied, giving 1 only when both are true;=FILTER(A2:A11, (B2:B11=F2)+(C2:C11=H2)) is OR: the sum is above 0 when at least one is true.COUNTIFS and SUMIFS help you count what was selected: build an "at least" condition from a cell like this: ">="&G2.
The trainer has no "Advanced" dialog. The criteria range F1:H2 is already set up: build the selection from it with formulas.
Test yourself
Practice is part of Pro
The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.