Sum, offset & match function

[kashaikh78]

My objective is to get year to date figures populated based on the month in cell O6 by transportation mode and by location.

I have used the below formula to get the YTD policies sold in summary tab (cell T31) but the same formula doesn't work when I tried to calculate the cost to the company (summary tab cell (U31). I thought the reason is that I have used table to calculate the policies sold and using the raw data for cost to the company calculation. I don't want to setup too many tables to get the YTD calculations therefore used the raw data.



=SUM(OFFSET($B$31,MATCH($A31,$A$31:$A$33,0)-1,0,,):OFFSET($B$31,MATCH($A31,$A$31:$A$33,0)-1,MATCH($O$6,$B$30:$M$30,0)-1,,))

The raw data is in tab "Workings". "Location" is in working tab column R; "Means of transport" is in column AF; "cost to the company" is in column AZ.

Can someone help me in tweaking the above formula to get it worked.

I have attached the spreadsheet of my workings