Oct 04 2019 08:32 PM
Hello Excel Community,
I am trying to improve my excel skill but I hit a roadblock here. I am trying to do research but I can't find a clear answer, here is my scenario;
1. I want to filter the fruits in Columns A-B with sales of $1500 - $2000. The results should be put in columns G-H. What is the formula to do this?
2. Using the same data, filter the fruits that have sales of $2000 and up. What is the formula to do this?
Thanks in advance for the help.
Oct 04 2019 09:48 PM
Hi,
You need to use the Advanced Filter to do so.
Please see this playlist to learn more about it.
I've done that by Advanced Filter in the solution file.
Regards
Oct 04 2019 10:11 PM
In the attached file, the formula in E3 is:
=IFNA(LOOKUP(2,1/(($B$2:$B$21=AGGREGATE(15,6,$B$2:$B$21/($B$2:$B$21<1500),ROW()-2))*
(COUNTIF(E$2:E2,$A$2:$A$21)=0)),
$A$2:$A$21),"")
In G3, the formula is modified like this:
=IFNA(LOOKUP(2,1/(($B$2:$B$21=AGGREGATE(15,6,$B$2:$B$21/(($B$2:$B$21>=1500)*
($B$2:$B$21<2000)),ROW()-2))*(COUNTIF(G$2:G2,$A$2:$A$21)=0)),
$A$2:$A$21),"")
In I3, the formula is again modified like this:
=IFNA(LOOKUP(2,1/(($B$2:$B$21=AGGREGATE(15,6,$B$2:$B$21/($B$2:$B$21>=2000),ROW()-2))*
(COUNTIF(I$2:I2,$A$2:$A$21)=0)),
$A$2:$A$21),"")
In F3, H3, and J3, this formula is copied therein:
=IFNA(LOOKUP(2,
1/($A$2:$A$21=E3),
$B$2:$B$21),"")
Oct 05 2019 07:29 AM
@Twifoo - thanks for your assistance on this.
I will review this now and study your approach.
I will get back to you just in case I have a question.
Oct 05 2019 07:30 AM
@Haytham Amairah - thanks for your assistance on this.
I will review this now and study your approach.
I will get back to you just in case I have a question.