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
Highlighted
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
Highlighted

@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

Highlighted

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

Highlighted

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

Highlighted

@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
IF statements and conditional formatting
clare1981 in Excel on
1 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
4 Replies