## List people with the highest scores based on criteria in excel

*Article updated on November 30, 2010*

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:**

Copy cell F2 and paste it down.

**Formula in G2:**

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

*.*

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article