Combining HLOOKUP and SUMIF into one formula

Hello,

I have a rather complex sheet which is collating tickets sold by cinema for multiple cinema chains.

It does this by having 3 worksheets;
sheet 1) the dashboard where the sales are collated - it is here that I want to solve the problem. Currently, the formula go the the second worksheet and SUMIF the data there
sheet 2) the PBYP sheet collates the sales using HLOOKUP from the 3rd sheet
sheet 3) titled MAN, this is where the data is stored, in columns with week ending dates

I want a formula in the dashboard that will let me;
a) combine the function of finding the dated column in sheet 3 and then summing the data in that column based on the criteria in Row A of sheet 3
b) this would in effect render sheet 2 useless, and given I have to have this work over 18 different datasets, that would help with filesize
c) I need to give this to a client for them to use, and as such - want to try and avoid macro or pivot if at all possible

I tried some use of SUMPRODUCT, and INDEX/MATCH - using the searches on the forum, but I am unfamiliar with them and when I tried to apply them (see the blue cells on sheet 1 - DASHBOARD) they failed to work.

Can anyone help by altering and reposting the formula in the attached - specifically the sheet named DASHBOARD and the blue cells I highlighted. From there, I should be able to configure the sheet further if I know what the formula should be.

Thank you in advance for the assistance,

Ian

Thank you Martin,
I posted it in and it works a treat - BUT - the value in the dated columns for Cineworld should be the difference between the sum of each week.

Am I right in thinking then that I need to have a sum, like this;

=(SUMIF(MAN!$B$2:$B$382,"*" & $I9 &"*",INDEX(MAN!$B$2:$M$382,0,MATCH(K$8,MAN!$B$1:$M$1,0))))-(SUMIF(MAN!$B$2:$B$382,"*" & $I9 &"*",INDEX(MAN!$B$2:$M$382,0,MATCH(L$8,MAN!$B$1:$M$1,0))))

Have posted in the attached, but I get stuck (all in the DASHBOARD sheet) after the sum in J9.
K9 seems to not return the correct value and as I don't reference the total for the week prior to w/e 15 June, I'm uncertain as to how to populate cell L9.

Have posted again the updated sheet with row 9 of DASHBOARD where I've implemented your brilliant help.

Thanks very much,

Ian

2014_15_REPORT TEST - Copy.xlsm

Just a friendly bump to see if anyone could help me over the line with my second query please.

Hello again all,

Resurrecting this old one as I want to now take the formula one stage further.

On the attached sheet, the DASHBOARD has sumif/index/match combinations in rows 10-19, both looking at the data in sheets MAN1 and MAN2.

Problem I have is when I replicate the dashboard grids and add data sheets, it's fiddly having to find/replace the worksheet names within the formulas (currently MAN1 & MAN2).

What I would like to do is have the DASHBOARD grid on the left look at the event code in cell G7, and find the worksheet called that, and the formula still work.

I understand an INDIRECT formula may work but I'm not sure how to integrate it into what is already a long formula.

I would love a steer on this one please?

Ian

CINEMA_2015-16_workings.xlsm