Andre asks:

I am tryng to list the people with the highest scores based on certain criteria.

My data:

column

A B C D

Mike 207 Yes Life
Greg 207 Yes Life
Sid 207 Yes Life
Greg 207 Yes Life
Greg 207 Yes Life
Sid 207 Yes Life
Greg 207 No Life
Sid 204 No Health

criteria
countif b= 207 and column c= yes and column d= Life

and it then needs to arange it from the highest to the lowest
(Large)

and then match it with the name eg, Greg Sid or Mike

so what i am looking for is eg.

Greg 3
Sid 2
Mike 1

but it has to be in one formula.

Answer:

Array formula in F2:

=IF(SUM(1/COUNTIF($A$2:$A$9, $A$2:$A$9))>=ROWS($A$1:A1), INDEX($A$2:$A$9, MATCH(LARGE(FREQUENCY(IF(COUNTIF($B$1, $B$2:$B$9)*COUNTIF($C$1, $C$2:$C$9)*COUNTIF($D$1, $D$2:$D$9), (COUNTIF($A$2:$A$9, "<"&$A$2:$A$9)+1), ""), COUNTIF($A$2:$A$9, "<"&$A$2:$A$9)+1), ROW(A1)), FREQUENCY(IF(COUNTIF($B$1, $B$2:$B$9)*COUNTIF($C$1, $C$2:$C$9)*COUNTIF($D$1, $D$2:$D$9), (COUNTIF($A$2:$A$9, "<"&$A$2:$A$9)+1), ""), COUNTIF($A$2:$A$9, "<"&$A$2:$A$9)+1), 0)), "") + CTRL + SHIFT + ENTER.

Copy cell F2 and paste it down.

Formula in G2:

=SUMPRODUCT(--(F2=$A$2:$A$9), --($B$1=$B$2:$B$9), --($C$1=$C$2:$C$9), --($D$1=$D$2:$D$9)) + ENTER. Copy cell G2 and paste it down as far as needed.

Try changing criteria in cell range B1:D1.
I wish I knew how to accomplish this in a pivot table. (Edit: Now I know:List people with the highest scores based on criteria in a pivot table (Excel 2007))

Download excel sample file

list the people with the highest scores based on certain criteria.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

LARGE(array,k) returns the k-th largest row number in this data set.

ROW(reference) returns the rownumber of a reference

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

FREQUENCY(data_array, bins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array.