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.

Office 1					100
	Office 1A	33
		HR	15
		Security	15
		Mgmt Decision	3
	Office 1B	20
		HR	9
		Security	8
		Mgmt Decision	3
	Office 1C	47
		HR	7
		Security	20
		Mgmt Decision	20

<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>
</tbody>

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