summary table using formula

[waratah2014]

Hi,
Hoping if someone can assist in resolving last piece of puzzle for me.

I have data in the excel sheet with I want to summarise in table form using 'sumif' / Vlook up.

I able to create a summary via PIVOT but want to replicate same layout using formula.

I am able to create a summary using formula but one last column with data and not able to bring across . That's where I need your help.

Main Data is in 'Others' Tab of the sheet.
Please refer Pivot Summary tab, which I am trying to replicate using formula

only CC column I need to bring across from 'Others' to the 'summary by formula' sheet using formula.

So I need a Column inserted anywhere in the 'summary by formula'

I am attaching a sheet with the data.

 

[Chihiro]

Why not add data to Data Model and use CUBE function?

 

[waratah2014]

its really good tip, was hoping if you can make changes made in the master data flows through in it ? is it possible

 

[Chihiro]

I'm not sure what you mean.
But if you are asking, if the formula reflects changes made to data source. Not exactly with this set up. Since expressions are hard coded.

If you want more dynamic set up. It would need PowerQuery to set up more robust data model.

Edit: For more clarity.