So, I have excel 2007 and data similar to the below. Each ID represents a case file and the color a type of error (green is no errors), so there may be more than one on the same case: ID Name Colour 12345 Dave Red 12345 Dave Yellow 12345 Dave Yellow 1578 Bob Red 1578 Bob Red 123 Dan Yellow 99999 Dave Yellow 99999 Dave Yellow 1587 Borris Green 5555 Borris Red There is a hierarchy of Red then Yellow so where both exist on a case, it counts as a red case. I want to produce a pivot table with the each unique name and their total number of unique cases, broken down into percentage (or count) of Red, Yellows and Greens. I've been trying to add helper columns to the underlying data but I cant get accurate results. Help!

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Help! Pivot table trouble with summarizing subsets in Excel 2007

Thread starter ChIpStIcK
Start date Dec 2, 2015
Tags

countif pivottables percent sumproduct unique combination

ChIpStIcK

New Member

Dec 2, 2015

So, I have excel 2007 and data similar to the below. Each ID represents a case file and the color a type of error (green is no errors), so there may be more than one on the same case:

ID	Name	Colour
12345	Dave	Red
12345	Dave	Yellow
12345	Dave	Yellow
1578	Bob	Red
1578	Bob	Red
123	Dan	Yellow
99999	Dave	Yellow
99999	Dave	Yellow
1587	Borris	Green
5555	Borris	Red

There is a hierarchy of Red then Yellow so where both exist on a case, it counts as a red case. I want to produce a pivot table with the each unique name and their total number of unique cases, broken down into percentage (or count) of Red, Yellows and Greens.

I've been trying to add helper columns to the underlying data but I cant get accurate results. Help!