LinkBack URL
About LinkBacks
Would like formula to extract values if
Criteria 1: extract only values in D2:D14 <=15
Criteria 2: extract only values in D2:D14 >=16
See attached file
Thanks
Would like formula to extract values if
Criteria 1: extract only values in D2:D14 <=15
Criteria 2: extract only values in D2:D14 >=16
See attached file
Thanks
Does this work for you? Make sure you hit refresh all on the data tab after you add any new lines of data to the table.
Extract Values.two.Criteria..xlsx
ARRAY FORMULA
Formula:
Please Login or Register to view this content.
-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Fotis1991 : the formula works for <=15 but does not work for >=16. Can you take a look pls?
Thanks
<=15
In Cell F5
=IF(ROWS(A$1:A1)>COUNTIF($D$2:$D$14,"<="&$G$1),"",INDEX($A$2:$A$14,SMALL(INDEX(($D$2:$D$14<=$G$1)*(ROW($A$2:$A$14)-ROW($A$2)+1),),COUNTIF($D$2:$D$14,">"&$G$1)+ROWS(A$1:A1))))
Copy down as far as desired
>=16
In Cell F18
=IF(ROWS(A$1:A1)>COUNTIF($D$2:$D$14,">="&$H$1),"",INDEX($A$2:$A$14,SMALL(INDEX(($D$2:$D$14>=$H$1)*(ROW($A$2:$A$14)-ROW($A$2)+1),),COUNTIF($D$2:$D$14,"<"&$H$1)+ROWS(A$1:A1))))
Copy down as far as desired
Both are non-array (standard) formulas
Life's a spreadsheet, Excel!
Say thanks, Click *
Ace_XL: great work - if there a way I can have 15 and 16 in a drop down and have one formula extract these values? See attached
Thanks
Excel book added
Aah I see..
Cell F5 remains same i.e.
=IF(ROWS(A$1:A1)>COUNTIF($D$2:$D$14,"<="&$G$1),"",INDEX($A$2:$A$14,SMALL(INDEX(($D$2:$D$14<=$G$1)*(ROW($A$2:$A$14)-ROW($A$2)+1),),COUNTIF($D$2:$D$14,">"&$G$1)+ROWS(A$1:A1))))
Copied down
F18 becomes
=IF(ROWS(A$1:A1)>COUNTIF($D$2:$D$14,">"&$G$1),"",INDEX($A$2:$A$14,SMALL(INDEX(($D$2:$D$14>$G$1)*(ROW($A$2:$A$14)-ROW($A$2)+1),),COUNTIF($D$2:$D$14,"<="&$G$1)+ROWS(A$1:A1))))
Copied down
Ace_XL: want it to show >=16 in cell F5:F15 if I select 16 from the drop down instead of a second formula from F18 and coping down. Pretty much nesting the two formulas together and coping down from F5:F15.
Thanks
Ok, if I've understood this correctly there are only two option sin the drop-down 15 & 16 and you want differing results based on what is selected
Try
=IF(ROWS(A$1:A1)>COUNTIF($D$2:$D$14,IF($G$1=15,"<=",">")&$G$1),"",INDEX($A$2:$A$14,SMALL(IF($G$1=15,INDEX(($D$2:$D$14<=$G$1)*(ROW($A$2:$A$14)-ROW($A$2)+1),),INDEX(($D$2:$D$14>$G$1)*(ROW($A$2:$A$14)-ROW($A$2)+1),)),COUNTIF($D$2:$D$14,IF($G$1=15,">","<=")&$G$1)+ROWS(A$1:A1))))
Ace_XL: great work friend! Works like a charm! Thanks a million
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks