summarize (pivot or otherwise) sales in region by salespersons when regional responsibilities change over months

[chintugudda]

Hi
Below is the excel file. I am looking to get a pivot or summarize the result. The sales data is by month and region. Salespersons are identified in a mapping table. Trying to find an easy way to summarize. In real life example, regions are not 4 but variable is 100s. So dont want to use manual formulas. Any ideas?

	A	B	C	D	E	F	G	H	I
1
2		Region	Jan	Feb	Total	Jan-Sales Person	Feb-Salesperson
3		East	100	120	220	Rick	Rick 50% Boss 50%
4		West	202	200	402	Tom	John
5		North	56	60	116	John	Boss
6		South	150	170	320	Boss	Jeb
7			508	550	1058
8		DESIRED OUTPUT
9			Rick	Tom	John	Boss	Jeb	Total
10		East	160					160
11		West		202				202
12		North			256			256
13		South				270	170	440
14			160	202	256	270	170	1058
15
16

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[soadfan]

Mark data, insert pivotTable, change display to classic pivot layout, change summarize of values columns as desired

 

[chintugudda]

Hi
I need the summaries by persons. FOr example, you will see John having 2 different regions in 2 months. Also, Boss has a 50% share in 2 nd month. Normally, i could simply do an excel file with manual formula's for each month. I am trying to see if there is a better solution around tha, where idont have keep doing it every month. Would consolidate multiple spreadsheet work?

 

[soadfan]

Well, to be honest i have a lot of experience as sales KAM and Supervisor. I've worked with tons of reports called STT (sales to trade).
Pivot is pretty powerful tool to manage/analyze data. In my opinion it should do the work. If it's too complex for Pivot, you should give a shot of Power Bi.