# Summarizing a data set using a pivot table.

#### PatrickO

##### New Member
Code:
``````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.

Code:
``````	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.

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I should mention, if at all possible, I would like to be using a pivot table to achieve these results.

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,

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:

Code:
``=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

Replies
1
Views
235
Replies
14
Views
918
Replies
6
Views
289
Replies
19
Views
606
Replies
1
Views
880

1,207,391
Messages
6,078,209
Members
446,321
Latest member
thecachingyeti

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back