Sum values based on operational timetable

Hi Guys,

I cant get this to work without creating VLOOKUP tables with cell references and then creating the indirect formulas to reference the correct cells, there MUST be a more eligant way!


Example Sheet attached

Sheet 1
Column 1: Dates
Row 1: Times in half hourly intervals

Data: Half Hourly recorded electricity usage



Sheet 2
Column 1: Dates
Row 1: Times in half hourly intervals

Data: Opening times (0=Closed, 1=Open)


I need a formula on sheet 1 Column AZ which will calculate the total operational usage for that day.

The formula will need to work as follows.

Lookup day
Match day to opening hours table
Sum all values on that row which correspond with the cells in sheet 2 containing "1"

Can't get my head around this one!

Any help appreciated

Thanks guys!