Can Powerpivot do this?

[JEB85]

I have Two Tables:</SPAN>
Database</SPAN>
Calendar - (includes 'Date', 'FiscalQTR', 'FiscalYR' and 'Period')</SPAN>

In the Database Table I have the following fields:</SPAN>
Contract</SPAN>
Code1</SPAN>
Code2</SPAN>
Reporting Month e.g. Sep-11, Oct-11, Nov-11 etc</SPAN>

I've created a relationship between 'Reporting Month' and 'Date' (Daily Dates in the 'Calendar' table) so in my pivot table I use FiscalYr and Period</SPAN>

What I want to be able to do, and I'm not sure if this is possible, is create a pivot table which will summarises the movement in position of our contracts</SPAN>

At the minute I have lookup formulas to the side of my pivot tables to establish a 'TYPE' of movement which basically say:</SPAN>

New = New Product on a new contract</SPAN>
Lost = Lost Product on a lost contract</SPAN>
Positive Churn = New Product on an existing contract</SPAN>
Negative Churn = Lost Product on an existing contract</SPAN>
Contract Transfer = Product transferred to different contract</SPAN>
Continuous = No movement</SPAN>

I then summarise the position through some sumproduct calculations.</SPAN></SPAN>

Example:</SPAN></SPAN>

[TABLE="width: 659"]
<TBODY>[TR]
[TD][/TD]
[TD]Fiscal Yr</SPAN></SPAN>
[/TD]
[TD]2011-12</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fiscal Yr</SPAN></SPAN>
[/TD]
[TD]2011-12</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Period:</SPAN></SPAN>
[/TD]
[TD]Sep-11</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Period:</SPAN></SPAN>
[/TD]
[TD]Oct-11</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Region:</SPAN></SPAN>
[/TD]
[TD]W</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Region:</SPAN></SPAN>
[/TD]
[TD]W</SPAN></SPAN>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TYPE</SPAN></SPAN>
[/TD]
[TD]Contract</SPAN></SPAN>
[/TD]
[TD]Code1</SPAN></SPAN>
[/TD]
[TD]Code2</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]TYPE</SPAN></SPAN>
[/TD]
[TD]Contract</SPAN></SPAN>
[/TD]
[TD]Code1</SPAN></SPAN>
[/TD]
[TD]Code2</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]001B</SPAN></SPAN>
[/TD]
[TD]ZZZ</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]001B</SPAN></SPAN>
[/TD]
[TD]ZZZ</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Contract Transfer</SPAN></SPAN>
[/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]002C</SPAN></SPAN>
[/TD]
[TD]ZZZ</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]003M</SPAN></SPAN>
[/TD]
[TD]ZZZ</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]003M</SPAN></SPAN>
[/TD]
[TD]ZZZ</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Contract Transfer</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]002C</SPAN></SPAN>
[/TD]
[TD]ZZZ</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Negative Churn</SPAN></SPAN>
[/TD]
[TD]A</SPAN></SPAN>
[/TD]
[TD]004Y</SPAN></SPAN>
[/TD]
[TD]YYY</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]399B</SPAN></SPAN>
[/TD]
[TD]CCK</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]399B</SPAN></SPAN>
[/TD]
[TD]CCK</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]486B</SPAN></SPAN>
[/TD]
[TD]PPO</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]486B</SPAN></SPAN>
[/TD]
[TD]PPO</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Positive Churn</SPAN></SPAN>
[/TD]
[TD]B</SPAN></SPAN>
[/TD]
[TD]899P</SPAN></SPAN>
[/TD]
[TD]AAM</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[TD]070E</SPAN></SPAN>
[/TD]
[TD]ABC</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[TD]070E</SPAN></SPAN>
[/TD]
[TD]ABC</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[TD]071T</SPAN></SPAN>
[/TD]
[TD]ABB</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[TD]071T</SPAN></SPAN>
[/TD]
[TD]ABB</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Negative Churn</SPAN></SPAN>
[/TD]
[TD]C</SPAN></SPAN>
[/TD]
[TD]996Y</SPAN></SPAN>
[/TD]
[TD]PQR</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]New</SPAN></SPAN>
[/TD]
[TD]D</SPAN></SPAN>
[/TD]
[TD]787U</SPAN></SPAN>
[/TD]
[TD]TTE</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Lost</SPAN></SPAN>
[/TD]
[TD]G</SPAN></SPAN>
[/TD]
[TD]998Y</SPAN></SPAN>
[/TD]
[TD]PQT</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD]New</SPAN></SPAN>
[/TD]
[TD]D</SPAN></SPAN>
[/TD]
[TD]799Y</SPAN></SPAN>
[/TD]
[TD]TTV</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]New</SPAN></SPAN>
[/TD]
[TD]D</SPAN></SPAN>
[/TD]
[TD]802R</SPAN></SPAN>
[/TD]
[TD]UHU</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Oct-11</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New</SPAN></SPAN>
[/TD]
[TD]3</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lost</SPAN></SPAN>
[/TD]
[TD]1</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Positive Churn</SPAN></SPAN>
[/TD]
[TD]1</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Negative Churn</SPAN></SPAN>
[/TD]
[TD]2</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Contract Transfer</SPAN></SPAN>
[/TD]
[TD]1</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Continuous</SPAN></SPAN>
[/TD]
[TD]6</SPAN></SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

The formulas for last month (in this case Sept-11) will only ever deliver 'Continuous', 'Contract Transfer', 'Negative Churn' or 'Lost'</SPAN></SPAN>
The formulas for the current month (in this case Oct-11) will only ever deliver 'Continuous', 'Contract Transfer', 'Positive Churn' and 'New'</SPAN></SPAN>
The 'Continuous' and 'Contract Transfer' for both months will always equal each other</SPAN></SPAN>

('Code1' relates to the type of product and 'Code2' is a unique reference for that product)</SPAN></SPAN>

At the minute I'm doing all of this through 2 pivot tables and having to change both the 'Period' and the 'Region' to update my calculations</SPAN></SPAN>

Is it possible to create formulas in Powerpivot which will enable me to summarise the position through one PowerPivot (having all the periods in the columns and the type as rows with FiscalYr, Period and Region as filters)?</SPAN></SPAN>

Thanks

 

[llenza]

Not 100% sure of what you are looking for but here I go!
If I understand somewhat you want to have all the data in one place
For example:

[TABLE="width: 336"]
<tbody>[TR]
[TD="class: xl26, width: 64, bgcolor: transparent"]Fiscal Yr
[/TD]
[TD="class: xl27, width: 64, bgcolor: transparent"]Period:
[/TD]
[TD="class: xl26, width: 64, bgcolor: transparent"]Region:
[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"]TYPE
[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"]Contract
[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"]Code1
[/TD]
[TD="class: xl24, width: 64, bgcolor: transparent"]Code2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]001B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ZZZ
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Contract Transfer
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]002C
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ZZZ
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]003M
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ZZZ
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Negative Churn
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]004Y
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]YYY
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]399B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]CCK
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]486B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]PPO
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]070E
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ABC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]071T
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ABB
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Negative Churn
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]996Y
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]PQR
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Sep-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Lost
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]G
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]998Y
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]PQT
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]001B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ZZZ
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]003M
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ZZZ
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Contract Transfer
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]002C
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ZZZ
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]399B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]CCK
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]486B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]PPO
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Positive Churn
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]899P
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]AAM
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]070E
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ABC
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]Continuous
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]C
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]071T
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]ABB
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]New
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]787U
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]TTE
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]New
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]799Y
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]TTV
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2011-12
[/TD]
[TD="class: xl28, bgcolor: transparent, align: right"]Oct-12
[/TD]
[TD="bgcolor: transparent"]W
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]New
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]D
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]802R
[/TD]
[TD="class: xl25, width: 64, bgcolor: transparent"]
UHU

[/TD]
[/TR]
</tbody>[/TABLE]

Now that all the data is on one sheet, create a pivot table. I have tested your sheet on my PC.
You can setup the Pivot Table to give you the following:
1. Summarize by Fiscal Yr (all or one or some)
2. Summarize by Period (all dates or one date or some dates)
3. Summarize by Region (all or one or some)
4. Summarize by Type and add types together with an overall amount
5. Illustrate Code1 and Code2 and add types together with an overall amount
6. Count Types only while maintaining the Fiscal Yr, Period and Region
See below:

[TABLE="width: 741"]
<tbody>[TR]
[TD]Count of Contract
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fiscal Yr
[/TD]
[TD]Period:
[/TD]
[TD]Region:
[/TD]
[TD]Code1
[/TD]
[TD]Code2
[/TD]
[TD]TYPE
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011-12
[/TD]
[TD="align: right"]Sep-12
[/TD]
[TD]W
[/TD]
[TD]001B
[/TD]
[TD]ZZZ
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]002C
[/TD]
[TD]ZZZ
[/TD]
[TD]Contract Transfer
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]003M
[/TD]
[TD]ZZZ
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]004Y
[/TD]
[TD]YYY
[/TD]
[TD]Negative Churn
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]070E
[/TD]
[TD]ABC
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]071T
[/TD]
[TD]ABB
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]399B
[/TD]
[TD]CCK
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]486B
[/TD]
[TD]PPO
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]996Y
[/TD]
[TD]PQR
[/TD]
[TD]Negative Churn
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]998Y
[/TD]
[TD]PQT
[/TD]
[TD]Lost
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 741"]
<tbody>[TR]
[TD]Count of Contract
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fiscal Yr
[/TD]
[TD]Period:
[/TD]
[TD]Region:
[/TD]
[TD]Code1
[/TD]
[TD]Code2
[/TD]
[TD]TYPE
[/TD]
[TD]Total
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2011-12
[/TD]
[TD="align: right"]Oct-12
[/TD]
[TD]W
[/TD]
[TD]001B
[/TD]
[TD]ZZZ
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]002C
[/TD]
[TD]ZZZ
[/TD]
[TD]Contract Transfer
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]003M
[/TD]
[TD]ZZZ
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]070E
[/TD]
[TD]ABC
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]071T
[/TD]
[TD]ABB
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]399B
[/TD]
[TD]CCK
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]486B
[/TD]
[TD]PPO
[/TD]
[TD]Continuous
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]787U
[/TD]
[TD]TTE
[/TD]
[TD]New
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]799Y
[/TD]
[TD]TTV
[/TD]
[TD]New
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]802R
[/TD]
[TD]UHU
[/TD]
[TD]New
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]899P
[/TD]
[TD]AAM
[/TD]
[TD]Positive Churn
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Not sure what you needed as the final product. Illustrated above is one pivot table for Oct and Sep. The Pivot Table can also combine the two. I did this on Excel 2000, but it would be easier in 2007 or 2010 with Table Link, so you can continue to add data and it calculates with
the formulas it already has. Good Luck!

 

[JEB85]

Thanks Llenza, my issue is creating the lookup formulas within the powerpivot grid, if I could do that there wouldn't be a problem. At the minute I'm just creating the lookup formulas in regular excel and so I'm limited to doing one month at a time!

 

[llenza]

Breakdown of a VLOOKUP formula:

Cell A3 =VLOOKUP(B3,E1:N16,2,0)

This is a test to learn to create VLOOKUP

Please the formula where you want the results to go
B3 represent the value to be matched (must be unique as it will stop looking when it finds it once)
E1:N16 represent the range of cells or all the cells in the table
2 represent the placement within the table

For example: when you have a table like E1 to N16. Column E is one, col. F(2) G(3) H(4) I(5) J(6) K(7) L(8) M(9) N(10)

Now, the number 2 represent not only the F Col. it also indicated that you want the VLOOKUP to find this value and compare it to B3

0 represents that you want an exact MATCH if Col 2 = B3 then you results will appear on A3

If I just confused you more let me know I will include table with this explaination. Good Luck! it took me a while to understand it myself. But you can do it!