Author: Oscar Cronquist Article last updated on November 30, 2010

### Overview

This post describes how to create a list of people, sorted by occurrence, based on criteria in a pivot table. In a previous post we created an array formula to accomplish the task.

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.

### Instructions excel 2007 - Create and setup pivot table

1. Select cell range A1:D9
2. Click "Insert" tab on the ribbon
3. Click "Pivot table" button
4. Choose where you want the Pivot table report to be placed
5. Click OK
6. Click and drag "Name" into "Row labels" window
7. Click and drag "Name" into "Values" window
8. Click and drag "Nr", "Yes/No" and Life/Health into "Report Filter" window.

Setup Report Filter

1. Select "207" in Report filter cell on the excel sheet
2. Select ""Yes" in Report filter cell on the excel sheet
3. Select ""Life" in Report filter cell on the excel sheet

Sort names by occurence

1. Click "Arrow" near "Name" on pivot table
2. Click "More sort options.."
3. Click "Descending (A to Z) by: Count of Name