Sumproduct with dynamic header and moving % column

[Rakhee Asarpota]

Hello all,
I need help to fix a formula...I am stuck
I have attached the file.

In short:
Eg: TC1 sheet has 2 sets of data:
12 months split by various activities and values, across months
19 Cost centres split by various activities and % against each.

on the Summary tab, i need to see the result by month, that should be dynamic to calculate: eg: SUMPRODUCT('TC1'!D$7:D$18,'TC1'!$R$7:$R$18) for CEO, I am unable to make it dynamic when a header is chosen from drop down.

I tried this and it should have worked, but it is giving me #value, not sure what I have missed:
=SUMPRODUCT('TC1'!E$7:E$18, OFFSET(INDIRECT(ADDRESS(ROW('TC1'!$R$3),COLUMN('TC1'!$R$3)+MATCH($B$2,'TC1'!$R$3:$AJ$3,0)-1,1,1,"TC1")),4,0,12))

Appreciate if someone can guide me.......thanks

 

[Haz]

In E7:
=SUMPRODUCT(INDEX('TC1'!$C$7:$O$18,,MATCH(E$6,'TC1'!$C$6:$O$6,0)),INDEX('TC1'!$R$7:$AJ$18,,MATCH($B$2,'TC1'!$R$3:$AJ$3,0)))

The blue portion looks for the month and returns all values, the green part looks for the Centre and returns the %s. Multiply and sum to get your results.

 

[Rakhee Asarpota]

Super Thank you....I understand now.....thanks again.
Are you able to also guide on this formula too:
If I have a date range:

start date: 01/07/2018; end date: 31/03/2019 = Total 8months (3months in 2019)
Value for the entire period is $4000.
I want to calendarise the value for Jan to Dec 2019 and showing values only from Jan-Mar (=$4000/8months), but to cease calculating after March.
As I have values that has different date ranges ending Dec 2019, aug 2019, sep 2019 and so on and so forth.

Appreciate your guidance.

thanks

 

[AlanSidman]

This appears to be a duplicate request of this thread.

https://chandoo.org/forum/threads/value-calculation-based-on-date-range.39621/

 

[bosco_yip]

Since the OP open a new post in post #.3 new question. (as per Alan's reported)

This post closed

Regards
Bosco