Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return multiple cells meeting a condition
How do I construct a formula to return multiple entries meeting a criteria?
I want the formula to return a vertical list of names where grade=2 or higher. Example: NAME GRADE Mike 3 Scott 2 Mary 1 Larry 2 -- Warm Regards, MikCra |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
return multiple cells meeting a condition
Try this array formula** :
Name = named range for the name column Grade = named range for the grade column Assume you want the list to start in cell D1. Enter this array formula** in D1: =IF(ROWS(D$1:D1)<=COUNTIF(Grade,"=2"),INDEX(Name, SMALL(IF(Grade=2,ROW(Name)-MIN(ROW(Name))+1),ROWS(D$1:D1))),"") Copy down until you get blanks ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "QC-Dude" wrote in message ... How do I construct a formula to return multiple entries meeting a criteria? I want the formula to return a vertical list of names where grade=2 or higher. Example: NAME GRADE Mike 3 Scott 2 Mary 1 Larry 2 -- Warm Regards, MikCra |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count filtered cells meeting a condition | Excel Discussion (Misc queries) | |||
Multiple Condition Text Return | Excel Worksheet Functions | |||
How to select certains records meeting a certain condition !!!! | Excel Worksheet Functions | |||
Determine number of rows meeting a condition | Excel Discussion (Misc queries) | |||
Count of unique items meeting condition | Excel Worksheet Functions |