pivot table question

[birkato]

Hi: I am somewhat new to pivot tables and have run into a problem which I think I can probably do with a pivot table but can't figure out how. Essentially I have a set of data, lets say I ask 10 people to rank their favorite four colors choosing from RED, BLUE, GREEN, and YELLOW. They are required to rank all colors as 1st through 4th. So my data block looks like:

NAME, 1ST, 2ND, 3RD, 4TH
NAME1, BLUE, GREEN, RED, YELLOW
NAME2, BLUE, RED, GREEN, YELLOW
NAME3, RED, GREEN, YELLOW, BLUE
and so on....

What I want is to know is to choose a color for the first rank and use it as a filter and be able to count the second rank colors. So I have a pivot table that uses a data filter for the 1ST RANK, then I have the second rank assigned to the "ROWS" and the "VALUES" categories and have it set up to COUNT the values. The table looks like this:

FILTER = BLUE

Row Labels SECOND RANK
BLUE 0
RED 1
GREEN 1
YELLOW 0
TOTAL 2

This is great, but I also would like to know what the counts are for the 3RD and 4TH ranks. I know that I can make a separate set of pivot tables and replace the "2ND RANK" rows and values with the 3rd and 4th rank, but is there a way to do all this in one table, something like this:

FILTER = BLUE

Row Labels SECOND RANK THIRD RANK FOURTH RANK
BLUE 0 0 0
RED 1 1 0
GREEN 1 1 0
YELLOW 0 0 2
TOTAL 2 2 2

I tried to do this by also placing the 3RD and 4TH rank columns in the VALUES box but it produces identical counts as the second rank.

 

[JasL]

Assuming data in A->E (ie NAME, 1ST, 2ND, 3RD, 4TH)
Add columns F>I , called; Red, Yellow, Green, Blue
Then add these array formula:
ARRAY FORUMA
F =SUM(IF($B2:$E2=F$1,{1,2,3,4},0))
G =SUM(IF($B2:$E2=G$1,{1,2,3,4},0))
H =SUM(IF($B2:$E2=H$1,{1,2,3,4},0))
I =SUM(IF($B2:$E2=I$1,{1,2,3,4},0))

Make sure you use CTRL+SHIFT+ENTER on the arrays formuals
Then pivot!

E.g.
1st Yellow

Row Labels Red yellow Green Blue
Person2 2 1 4 3
Person3 3 1 4 2
Person4 4 1 2 3
Grand Total 9 3 10 8

lowest = 1st, highest = Last