List people with the highest scores based on criteria in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Sort values > List people with the highest scores based on criteria in excel

← Previous post -

Filed in Excel, Sort values on Oct.18, 2010. Email This article to a Friend

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.

List people with the highest scores based on criteria in a pivot table (Excel 2007)

Unique distinct list sorted based on occurrance in a column in excel

Create unique distinct list sorted based on text length using array formula in excel

Filter unique distinct list sorted based on sum of adjacent values using array formula in excel

Lookup Unique based on Multiple Conditions