How do I calculate a sum in horizontal cells and display the results vertically in a table

I am trying to calculate weekly sales by Branch by week or by date to track who is the top selling Branch every week. Although this data is captured horizontally I would like it summarised and displayed vertically in a table as it makes for easier reading and pasting into reports.

I have attached a very simplistic mock-up of how the data is captured and how I plan to do this. The actual example extends to 52 weeks with approx 30 Branches, but that could grow.

I'm thinking of using a spinner button change the week / date, but how I do calculate a dynamic total in this way for each Branch? Also, I am trying to show the 'rank' of the Branch and how it performs from week to week - is it possible to display the movement of rank position using conditional formatting with arrows as used in the attached example.

I am using Excel 2003 for this workbook

Thanks for your help

D2 drag down
=SUM(OFFSET($C$2,1,MATCH($B14,$C$2:$AR$2,0)+1,5,1))
You can do that for each of your tab and the summrized using this example
http://www.excelforum.com/excel-gene...-indirect.html
make sure that you branches matches(now you have got "Branch1" in first row and "Branch 1" for summary