Hi Excellers yet another Friday so that means Formula Time!….yay….so today…something that I have been looking at recently as asked by one of my Newsletter subscribers- that is– comparing the volumes of  product sales when that product is on promotion in stores between certain dates it involves a formula.  Let’s start with some example data.

 

If you want to download the example file for this blog post you can do so right here.

[ddownload id=”7477″]

I have product sales for Beanie Bags and Beanie Hats.  Both products have been on promotion for a number of dates in stores and we want to the sales for the promotional dates.Let’s get started……by looking at our data.

 

 

To find the sales volumes for the sales dates we first need the sales dates.  Which are below

 

 

 

 

 

 

 

 

The formula we are going to use is the SUMIFS formula.  SUMIFS formula takes a range for summing the values and at least one criteria range and criteria. You can specify as many as 127 conditions for summing your data-  which by the way only works with Excel 2007 onwards.

 

So, we have a few conditions to meet to find the sales volumes.We want to find out the volumes of sales for Beanie Bags between the dates 01/01/2015 and 02/01/2015 and Beanie Hats between 01/02/2015 and 31/03/2015.

The syntax of the SUMIFS formula isSUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

lets build the formula…..

=SUMIFS($E$5:$E$23,$C$5:$C$23,”>=”&I5,$C$5:$C$23,”<=”&J5,$D$5:$D$23,H5)

 

So, in English this formula says, sum the values in E5 to E23, if the values in C5 to C23 are greater  than or equal to I5, and the values in C5 to C23 are less than or equal J5 and the values in D5 to D23 are equal to H5.

 

 

 

 

 

I want to drag or auto fill the formula down to find out the promotional volumes for Beanie hats also so I have made the data range absolute using the $ symbol.  Read more about absolute and relative cell references here.

 

So, sales of Beanie Bags when on promotion were 355 and Beanie Hats 669 as Excel has checked for a 3 conditions and given the sum of the sales for only meet those conditions.

 

Want to Learn More Formulas? Join the Excel Formulas Crash CourseIf you want to learn SUMIFS, SUMPRODUCT, OFFSET and 40 other day to day formulas, then consider Chandoo’s Excel Formula Crash Course. It has 31 lessons split in to 6 modules and makes you awesome in Excel formulas.  Formula Friday- How To Pick A Winner Using Excel

Formula Friday- Find The k-th Largest Value In Your Excel Data Set Using The LARGE Function

Formula Friday- Use the LEN Formula To Highlight Issues With My Data Import