Multiple selection criteria
Try this array formula**. Note that this is slow to claculate if you have
1000's of rows of data.
Named ranges:
Name, refers to A2:A100
Status, refers to B2:B100
Enter a formula i a cell that returns the count of records that meet the
criteria.
E1: =COUNTIF(Status,"certified")
Extract the names where the status is certified.
Array entered** in E2:
=IF(ROWS(E$2:E2)<=E$1,INDEX(Name,SMALL(IF(Status=" certified",ROW(Name)),ROWS(E$2:E2))-MIN(ROW(Name))+1),"")
Copy down until you get blanks.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
--
Biff
Microsoft Excel MVP
"Bigkahuna" wrote in message
...
No I want to have a column with the names of those who meet multiple
criteria. I actually need several columns with employees who meet
difference
sets of criteria so auto filter won't provide what I need.
"Pimamedic" wrote:
What about trying Filter Auto FIlter
"Bigkahuna" wrote:
I have a worksheet with employee name, whether they are certified and
their
function in separate columns. I want to use a formula to select only
those
employees who are certified and perform the audit function and put the
results in a separate column. Is there a simple way to perform this
operation? I seem to remember seeing an article relative to this but
can't
put my hands on it now.
Thanks
|