Exec Summary creation

[millerkd]

I have a spread sheet of vacant billets that is primarily organized by offices/sub-offices and then organized by statuses. I have been requested, to provide an executive summary for all of the sub-offices that shows the status of each vacant billet by status and by sub-office (See Below).

So I have office 1, and it is split into 1A, 1B, and 1C. within each sub-office there are multiple vacant billets that are in any number of statuses (i.e. with mgmt for decision, in security, with HR, etc.) these statuses are all captured in one of the collumns. What I need to provide is a status at the top of my report something like below.

[TABLE="class: grid, width: 500"]
<colgroup><col style="width:48pt" span="2" width="64"> <col style="mso-width-source:userset;mso-width-alt:3657;width:75pt" width="100"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2523;width:52pt" width="69"> <col style="width:48pt" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Office 1
[/TD]
[TD="width: 64"] [/TD]
[TD="width: 100"] [/TD]
[TD="width: 64"] [/TD]
[TD="width: 69"] [/TD]
[TD="width: 64, align: right"]100[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 3"]Office 1A[/TD]
[TD="align: right"]33[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]HR[/TD]
[TD="align: right"]15[/TD]
[TD] [/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Security
[/TD]
[TD="align: right"]15[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Mgmt Decision[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 3"]Office 1B[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]HR[/TD]
[TD="align: right"]9[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Security[/TD]
[TD="align: right"]8[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Mgmt Decision[/TD]
[TD="align: right"]3[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 3"]Office 1C[/TD]
[TD="align: right"]47[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]HR[/TD]
[TD="align: right"]7[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Security[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]Mgmt Decision[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Is there a way for me to provide this with out going through and counting each one manually?

I have thought about using a pivot table but I am not sure how to set it up. Any advice would be greatly appreciated.

-Kris

 

[CatOnASpreadsheet]

Hi Kris,

With your current format, it would be quite a difficult task to provide the sort of summary you require - especially since it also looks like you have a number of merged cells in there. If possible, I would recommend reorganizing the data into something like the following format:

Office 1 | Office 1A | HR | 15
Office 1 | Office 1A | Security | 15
Office 1 | Office 1A | Mgmt Decision | 3
Office 1 | Office 1B | HR | 9

etc. The pipes are meant to symbolize different cells.

That will allow you to use either a pivot table or a number of SUMIF / SUMIFS formulas to easily provide the required results.

I hope this helps.

Cheers,
Catalin