I'm trying to create a formula which calculates the sum an array of fields based upon multiple criteria. This relates to various franchises and the promotions which they run during the year
My document contains two sheets with the following information within in it -
- 'Daily Revenues'!A:A - date. There is one entry per franchise per day with the revenue for that specific day.
- 'Daily Revenues'!B:B - the franchise name
- 'Daily Revenues'!D:D - number. The daily revenue for that day.
- 'Promotions'!I:I - date. The start date of the promotion
- 'Promotions'!J:J - date. The end date of the promotion
- 'Promotions'!K:K - the franchise name
In short, we want to calculate the total revenue during each individual promotion period ('Daily Revenues'!I:I to Daily Revenues'!J:J) which each franchise has completed. We would want the total revenue to be on the same row as the details of the promotion (in 'Promotions'!O1, for example) As we have records of each promotion which each store has completed so far this year, we have 1,478 instances which makes manual calculation out of the question.
Here are two screenshots of sample worksheets. Note that some of the information I have xxx'ed out in order to maintain the columns in the example.
Daily Revenues
Promotions
In Column O in the Promotions tab, we would want to calculate the total revenues (from Daily Revenues shet) for the days from the duration in columns I and J.
I've tried various SUMIFS formulas, but haven't been able to get any results so far. Can anyone help figure this out?