The updated spreadsheet for today’s post is here. Please head over to the “IFS” tab to understand the power and potential application of SUMIFS and COUNTIFS.
Understanding SUMIF and COUNTIF
The first table is a simple summary of the distributor’s net sales and number of transactions to achieve the net sales. This can be achieved by simply using a SUMIF or COUNTIF function. But, for the sake of simplicity, I am going to stick with SUMIFS and COUNTIFS since they work for all scenarios. So, the formula structure for SUMIFS is
SUMIFS(Data you want to sum i.e. Net Sales, Data you want to filter i.e. Distributors list, Criteria for filter i.e. Distributor name)
Similar application for for COUNTIFS, AVERAGEIFS. I haven’t shown AVERAGEIFS here as I haven’t used it all that much in practice but it’s just an extension of SUMIFS and COUNTIFS.
Power of SUMIFS
The real power of the SUMIFS function is illustrated in the above table. You have now taken the data dump in the “Base” sheet and converted it into a heat map to understand which distributor-valve combinations generate the most revenue within 3 minutes.
There are many ways of cutting this data and it really depends on what you are trying to do. No matter how you plan to summarize it, SUMIFS/COUNTIFS/AVERAGEIFS can usually help. For example, you could just as easily focus on which distributor-valve combinations aren’t giving you enough sales.
Next week, we’ll take a look at how to set up absolute and relative references in such analysis before diving into conditional formatting and other ways to use this data.
A Learning a Day 