How can i use a Median+If formula with multiple 'if' criteria

Hello,

I need to analyze data for pay per click campaigns to sell musical (theatre) play tickets. We want to find the campaign that has the best ROI by month by play for the lead time between date of sale and date of the 'event'. I have a block of data that is essentially as follows:

Play name,
Campaign source,
Date Sold,
Date of event,
Lead time of sale (this is a calculation of the # of days between when a sale is made and the event actually occurs).
$ Amount of ticket

I am wanting to have a summary that has the list of events vertically, with the months of the year horizontally and want to read the data and for each month/event combination pull the median of all the source date for event by month.

I can do this if i create formula keys on my data set that has a separate column for each event+leadtime+month of sale but that is an enormous amount of formulas for something i think i should be able to do via an array/Median If.

Anyone run into this before?

I sure appreciate the help.

Thanks!

Thanks for the fast response Glen!

I have attached the sample workbook with a 'Summary' tab, a 'Data' tab and a lookups tab i used to dummy up the data.

On the summary tab, i am showing my 'end goal' which is to show the lead time for ticket purchases in a number of ways. I can get the figures to work for the 'overall' lead time for one of the data sets, the example shows it with 'Lead Time by Event' and the 'Overall' is complete.

What i cannot seem to get a clean process for is to also show it by month from the same data set and that is what i'm hoping for.

I look forward to hearing back from you!

Loren

That is excellent Bo, thanks!

I did make one slight change so that the dates being looked at are the purchase dates and not the event dates but otherwise works perfectly!

Thank you!

Loren