Home

Excel Help

%3CLINGO-SUB%20id%3D%22lingo-sub-894699%22%20slang%3D%22en-US%22%3EExcel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894699%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Excel%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20improve%20my%20excel%20skill%20but%20I%20hit%20a%20roadblock%20here.%20I%20am%20trying%20to%20do%20research%20but%20I%20can't%20find%20a%20clear%20answer%2C%20here%20is%20my%20scenario%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20I%20want%20to%20filter%20the%20fruits%20in%20Columns%20A-B%20with%20sales%20of%20%241500%20-%20%242000.%20The%20results%20should%20be%20put%20in%20columns%20G-H.%20What%20is%20the%20formula%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E2.%20Using%20the%20same%20data%2C%20filter%20the%20fruits%20that%20have%20sales%20of%20%242000%20and%20up.%20What%20is%20the%20formula%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%20for%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-894699%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894739%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894739%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420500%22%20target%3D%22_blank%22%3E%40ian_122282%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20use%20the%20%3CSTRONG%3EAdvanced%20Filter%3C%2FSTRONG%3E%20to%20do%20so.%3C%2FP%3E%3CP%3EPlease%20see%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DyAbRJHkK4Mg%26amp%3Blist%3DPLA6E69131CB6CD9C1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Eplaylist%3C%2FA%3E%20to%20learn%20more%20about%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20done%20that%20by%20Advanced%20Filter%26nbsp%3Bin%20the%20solution%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-894749%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-894749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420500%22%20target%3D%22_blank%22%3E%40ian_122282%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20E3%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFNA(LOOKUP(2%2C1%2F((%24B%242%3A%24B%2421%3DAGGREGATE(15%2C6%2C%24B%242%3A%24B%2421%2F(%24B%242%3A%24B%2421%26lt%3B1500)%2CROW()-2))*%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E(COUNTIF(E%242%3AE2%2C%24A%242%3A%24A%2421)%3D0))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24A%242%3A%24A%2421)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIn%20G3%2C%20the%20formula%20is%20modified%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFNA(LOOKUP(2%2C1%2F((%24B%242%3A%24B%2421%3DAGGREGATE(15%2C6%2C%24B%242%3A%24B%2421%2F((%24B%242%3A%24B%2421%26gt%3B%3D1500)*%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E(%24B%242%3A%24B%2421%26lt%3B2000))%2CROW()-2))*(COUNTIF(G%242%3AG2%2C%24A%242%3A%24A%2421)%3D0))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24A%242%3A%24A%2421)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIn%20I3%2C%20the%20formula%20is%20again%20modified%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFNA(LOOKUP(2%2C1%2F((%24B%242%3A%24B%2421%3DAGGREGATE(15%2C6%2C%24B%242%3A%24B%2421%2F(%24B%242%3A%24B%2421%26gt%3B%3D2000)%2CROW()-2))*%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E(COUNTIF(I%242%3AI2%2C%24A%242%3A%24A%2421)%3D0))%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24A%242%3A%24A%2421)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIn%20F3%2C%20H3%2C%20and%20J3%2C%20this%20formula%20is%20copied%20therein%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFNA(LOOKUP(2%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E1%2F(%24A%242%3A%24A%2421%3DE3)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24B%242%3A%24B%2421)%2C%22%22)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895009%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895009%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B-%20thanks%20for%20your%20assistance%20on%20this.%3C%2FP%3E%3CP%3EI%20will%20review%20this%20now%20and%20study%20your%20approach.%3C%2FP%3E%3CP%3EI%20will%20get%20back%20to%20you%20just%20in%20case%20I%20have%20a%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-895010%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-895010%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B-%26nbsp%3Bthanks%20for%20your%20assistance%20on%20this.%3C%2FP%3E%3CP%3EI%20will%20review%20this%20now%20and%20study%20your%20approach.%3C%2FP%3E%3CP%3EI%20will%20get%20back%20to%20you%20just%20in%20case%20I%20have%20a%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E
ian_122282
Occasional Contributor

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.

4 Replies

@ian_122282

 

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

@ian_122282 

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),"")

@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.

@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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies