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.

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:

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

Download excel sample file for this tutorial.

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

Thanks David, your excel file showed me how!!

Related posts:

List people with the highest scores based on criteria in excel

Extract a unique distinct list by matching items that meet a criterion in excel 2007 using pivot table

Excel 2007 pivot table: Count unique distinct records (rows)

Change pivot table data source using a drop down list

Why use Pivot table and pivot chart?