How do I do a SUMPRODUCT formula between a normal data list and a different pivot table?

I want to do a SUMPRODUCT formula but with a difference. Both rows of data need to be able to have the column length changed so that I can run Month To Date calculations (ie. I can change the value of one cell to alter the size of the array). I have managed to do this with the formula "=SUMPRODUCT((OFFSET(B8,0,0):OFFSET(B8,0,'Market Mix Summary'!$B$3)),(OFFSET(B43,0,0):OFFSET(B43,0,'Market Mix Summary'!$B$3)))", B3 is the cell used to alter the column (obviously).

However, the problem is that the second argument of the SUMPRODUCT function does not use a similar table to the first argument and uses a pivot table, although the column data are in the same order the row data isn't. I think I need to insert a Lookup function (or something) so that it can automatically find the correct data to use.

I hope this makes sense and someone hope you can help.

Thanks,

Peter,
Australia

PS: I also need to divide the data by the sum of the first aurgument but I think this is pretty simple, isn't it? By the way this is being used to calculate average monthly room rates for a hotel on a MTD basis (Monthly Rooms Sold x Monthly Average Rate / MTD Rooms Sold).