Formula Friday #12
Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.
SUMIFS is hands down the function I use the most
Once you get your head around how it works, and what it can do, you won’t be able to not use it.
I like to think of SUMIFS at the original Pivot Table. Mostly because I started using SUMIFS before I knew what a Pivot Table was. But also because it can do all the things a Pivot table can do. Without all the annoying reformatting with each refresh.
When to use the SUMIFS Function
Whenever you need to add things together based on a specific set of criteria. The same way COUNTIFS can be used to count values based on a specific set of criteria.
The main difference between SUMIFS and COUNTIFS is that you need to tell Excel what to add together, and then specify the criteria to match.
The formula you’ll need will look something like this
=SUMIFS(‘SS19’!$AK:$AK,‘SS19’!$B:$B,SUMMARY!$B$2,‘SS19’!$D:$D,SUMMARY!$B6)
In this example we want to know what the total buy units in January for each product category.
First we have to select our ‘sum_range’. This will let Excel know what we want to add together. In this example it’s the buy units, column AL, on the SS19 tab. Next we have to set the criteria Excel needs to match before returning the final value.
The order that you select your criteria in doesn’t really matter, but it’s always good practice to keep it as logical as possible.
In this example we want to know the totals by month by category. Which is why the first criteria selected is the month, column B on the SS19 tab. Overall it will be the biggest value, it will be the sum of all parts that come beneath it. The month value it needs to match is in cell B2 on the Summary tab.
Then we want to slice the total buy units for the month, into the different subtotals for each category in column D on the SS19 tab. And this needs to match each of the values on the Summary tab starting from B6.
For the option count we only need to include the month column and the category column, as Excel only needs to count the number of times these two criteria appear together.
=COUNTIFS(‘SS19’!$B:$B,SUMMARY!$B$2,‘SS19’!$D:$D,SUMMARY!$B6)
Let’s say you now have to condense your summary even more
By grouping categories together based on type. For example Short Dresses and Maxis become Dresses. While Shorts, Trousers and Skirts become Bottoms.
We could add in a formula that says “=this cell+that cell“, but that means every time we change the order of the categories in the summary table. We would have to change the selected cells to add together. Which is a no from me.
What we can do instead is use an array constraint so that the formula is dynamic, not impacted by any changes to the order of our summary table and means Excel can do all the heavy lifting.
This is the formula we need for the total buy units of Dresses (Short dresses and Maxis)
=SUM(SUMIFS(‘SS19’!$AK:$AK,‘SS19’!$B:$B,SUMMARY!$B$2,‘SS19’!$D:$D,{“SHORT DRESSES”,“MAXIS”}))
The only changes we make to the previous formula we started with, is to add =SUM to the beginning. And add in curly brackets { } to where we had previously selected the cell for each of the categories on the Summary tab.
Final thoughts
SUMIFS will definitely be a function to revisit in future posts. It is a key component when pulling together data summaries and trade performance dashboards.
But for now please add the above example, with the array constraint, to all your formulas that need it. I know from experience your sign off docs should be the first thing you review!
Until next time,
Ax
