Summarizing a data set using a pivot table.

[PatrickO]

Job	Name	Role	M1	M2	M3
X	Bob	Mngr	15	15	15
X	Sue	Sup.	10	10	10
X	Jon	Anlst	20	20	20
Y	Sue	Sup.	10	10	10
Y	Rick	Anlst	20	20	20
Y	Bob	Mngr	5	5	5
X	Jim	Mngr	20	20	0
Y	Phil	Sup.	0	0	20

This data set represents how employees times are allocated to certain projects (X and Y) through the given months (M1, M2, M3). I have their name and their level (Manager, Supervisor, Analyst). What I would like to achieve is a summary of how many managers, supervisors and analyst are being utilized in a given month in total, regardless of the project they are working on.

The following table would be the results I am looking for.

	M1	M2	M3
Mngr	2	2	1
Sup.	1	1	2
Anlst	2	2	2

I have attempted to utilize a pivot table but am unable to recreate the desired results. What is throwing me for a loop is that Sue appears in two lines, so when I try to count managers, it is double counting her.

Any help is greatly appreciated.

 

[PatrickO]

I should mention, if at all possible, I would like to be using a pivot table to achieve these results.

 

[shawnhet]

The quick and dirty way I was able to make this work was the following. First, replace all zeroes with blank spaces. Then take a pivot table of your data with the name and role in the row section and Count of M1,M2 & M3 in the Column section(on the layout page). Then(if needed) format your pivot table so M1,M2 &M3 are in different columns.

Once done, you can make a pivot table of the first pivot table with Role in the Row section and M1, M2 and M3 in the column section. This will give you the right answer, but will add a column for blanks as well(Pivot Table 1 will have 1 blank line generated for each Name).

Cheers,

 

[Matty]

Hi,

A formula solution, though I note that this isn't your preferred method.

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-family:Arial Unicode MS; text-align:left; ">Job</td><td>Name</td><td>Role</td><td>M1</td><td>M2</td><td>M3</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="font-family:Arial Unicode MS; text-align:left; ">X</td><td>Bob</td><td>Mngr</td><td style="text-align:right; ">15</td><td style="text-align:right; ">15</td><td style="text-align:right; ">15</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="font-family:Arial Unicode MS; text-align:left; ">X</td><td>Sue</td><td>Sup.</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="font-family:Arial Unicode MS; text-align:left; ">X</td><td>Jon</td><td>Anlst</td><td style="text-align:right; ">20</td><td style="text-align:right; ">20</td><td style="text-align:right; ">20</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="font-family:Arial Unicode MS; text-align:left; ">Y</td><td>Sue</td><td>Sup.</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td><td style="text-align:right; ">10</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="font-family:Arial Unicode MS; text-align:left; ">Y</td><td>Rick</td><td>Anlst</td><td style="text-align:right; ">20</td><td style="text-align:right; ">20</td><td style="text-align:right; ">20</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="font-family:Arial Unicode MS; text-align:left; ">Y</td><td>Bob</td><td>Mngr</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="font-family:Arial Unicode MS; text-align:left; ">X</td><td>Jim</td><td>Mngr</td><td style="text-align:right; ">20</td><td style="text-align:right; ">20</td><td style="text-align:right; ">0</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="font-family:Arial Unicode MS; text-align:left; ">Y</td><td>Phil</td><td>Sup.</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">20</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>
</td><td>M1</td><td>M2</td><td>M3</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td>Mngr</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">1</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td>Sup.</td><td style="text-align:right; ">1</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td>
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td>Anlst</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td>
</td><td>
</td></tr></tbody></table>
Array formula in B12 is:

=SUM(IF(FREQUENCY(IF($C$2:$C$9=$A12,IF(D$2:D$9>0,MATCH($B$2:$B$9,$B$2:$B$9,0))),ROW($B$2:$B$9)-ROW($B$2)+1),1))

Which needs committing with CTRL+SHIFT+ENTER and can then be copied down and across.

Matty