Formula to find sales for fixed event at a certain sales date

Hello,
Thanks for reading.
I am trying to create a live event sales grid which finds the value of sold tickets on a performance (set date & time) but at a certain date.
I want to change the date in cell B2 of the 'perf by perf' tabe, and have the sheet find the value corresponding to that date, for each performance, on sheet 'sold'. the multiple criteria is what is tripping me up.
The data is in a sheet where the row is the performance and the column is the sales at weekly intervals.
I need it to look for the exact performance date/time as with hlookups, when I add a row to sum a weekly range - the formulas all 'shift' and I have to rebuild them.
I have attached a sample sheet with what I am trying to do - in tab 'perf by perf' cell C6, I have a formula which has failed in several iterations.
That cell should return the value of tickets for that performance (in cell B6) at 22 June. The answer should be 131 tickets as in the tab 'sold'.
Can anyone help me please?
I'll be then using the formula to also pull in comps, gross, etc - once I know and understand any supplied solutions.
Cheers,
iantix

TOUR SALES.xlsx

Hi,

Have you considered a Pivot Table which is IMO much simpler and much more useful. See attached.

c6=INDEX(sold!$E$4:$G$32,MATCH($A6&$B6,INDEX(sold!$A$4:$A$32&sold!$C$4:$C$32,0),0),MATCH($B$1,sold!$E$1:$G$1,0))

Please Login or Register  to view this content.

try this and copy towards down

You are welcome and thanks for your feedback