Search code examples
excelexcel-formulaexcel-2011

Excel SUMIFS of cells based upon multiple criteria

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 Daily Revenues

Promotions 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?

Solution

  • Enter the following formula in Cell O2

    =SUMIFS('Daily Revenues'!$D$2:$D$13,'Daily Revenues'!$B$2:$B$13,"="&K2,'Daily Revenues'!$A$2:$A$13,">"&I2,'Daily Revenues'!$A$2:$A$13,"<"&J2)

    and drag/copy down as required. Change the range in formula as per your data.

    Above formula will give you Revenue for aFranchise between the Start Date and End Date excluding these dates. For example, between 01/01/16 and 07/01/16 will give result for 02/01/16 to 06/01/16i.e 02,03,04,05,06. If you want to include start and end date use the following formula:

    =SUMIFS('Daily Revenues'!$D$2:$D$13,'Daily Revenues'!$B$2:$B$13,"="&K2,'Daily Revenues'!$A$2:$A$13,">="&I2,'Daily Revenues'!$A$2:$A$13,"<="&J2)