How do I love thee? Let me COUNT the ways
COUNT Functions are not often used by Merchandisers. It definitely won’t be listed on a job description as a “needs to be able to”. However, there are 4 COUNT Functions every Merchandisers should know.
These 4 COUNT Functions will prove themselves useful when compiling performance summaries for weekly or monthly trade updates, product sign off and lessons learnt meetings. In this post I will give an overview of each of the 4 COUNT Functions, as well as provide an example to demonstrate their use.
What are COUNT Functions?
Count functions are predefined formulas that perform count calculations based on the instructions given. The 4 count functions every merchandiser should know are COUNT, COUNTA, COUNTIF and COUNTIFS. Each of these COUNT functions has a predefined formula, which dictates the criteria and structure,the given instructions need follow in order for the calculation to return the desired result.
Why are COUNT Functions Important?
Count functions are important for Merchandisers because they can be used to assess the balance of a range. For example, comparing if the sales contribution percentage is inline with the percentage mix of options. This comparison makes it possible to highlight where there product categories are over or underperforming.
Count functions can also perform more complex calculations, which include specific product attribute, such as assessing the percentage mix of printed, long sleeve blouses within the total blouse options.
When to use COUNT Functions?
If you are new to COUNT Functions a good place to introduce them would be within Line Detail, Sign Off Packs or Lessons Learnt category performance summaries. As well as Strategy Packs. In each of these cases the focus will be on assessing the performance (or forecasted performance) of a product, or product attribute, based on the percentage of how many are within the whole range.
Let's look at each COUNT function in turn
COUNT, COUNTA, COUNTIF and COUNTIFS are the 4 COUNT Functions every Merchandiser should know. Each of these Functions has a predetermined formula that will perform count calculations based on the structure and criteria of the instructions.
1. COUNT
Example: Counting the number of product categories within a department using the numerical category ID.
Formula Structure: =COUNT(AD2:AD12)
Formula Criteria: cells with numerical values will be counted
In this example the category IDs are in a single list, making it possible to make a selection of a complete range. If the IDs were placed more randomly the formula structure would look more like this =COUNT(AD2,AD5,AD8). Where each cell to include in the calculation is selected individually and separated with a comma “,”.
2. COUNTA
Because the predefined formula within the COUNTA Function looks for cells that are not blank. It makes possible to use in circumstances where the values in the cells of the given range include both numerical and non-numerical values such as Line numbers, SKUs and manufacturer part numbers.
Example: Counting the number of product categories within a department using the category name.
Formula Structure: =COUNTA(AC2:AC12)
Formula Criteria: cells that are not blank will be counted
In this example the category IDs are in a single list, making it possible to make a selection of a complete range. If the IDs were placed more randomly the formula structure would look more like this =COUNTA(AD2,AD5,AD8). Where each cell to include in the calculation is selected individually and separated with a comma “,”.
3. COUNTIF
Example: Counting the number of options planned for each store grade.
Formula Structure: =COUNTIF(LD!$AG$11:$AG$199,D5)
Formula Criteria: count cells in data range LD!AG11:AG199 that match the value in cell D5
In this example the formula has been used to count how many options have been attributed to the ‘online exc’ store grade on the ‘LD tab’. Because the calculation is dependent on matching the given criteria, the COUNTIF function is more flexible than the COUNT or COUNTA Functions as it can accommodate any specified data entry.
4. COUNTIFS
Example: Counting the number of options planned for each store grade per product category
In this example the formula has been used to count how many Duffle coats have been attributed to the ‘online exc’ store grade on the ‘LD tab’.
Tips and Reminders for COUNT Functions
A couple of things to keep in mind when it comes to using COUNT Functions.
Whether you are using COUNT, COUNTA or COUNTIF/S the predefined formula will perform a calculation based on the count of cells that meet the given criteria. If you need the sum of cells, you will want to choose one of the SUM Functions.
The most common error message when using COUNTIFS is #VALUE. This error will appear when there is a mismatch between the sizes of the selected range criteria. For example =COUNTIFS(LD!$M$11:$M$199,OPTIONS!$B$27,LD!$AG$11:$AG$190,D30).
If this has happened when you’ve used the COUNTIFS function, remember to check whether each of the data ranges selected, to match each of the given criteria, include the same number of cells to reference.
The 4 COUNT Functions every Merchandiser should know can deliver insight to drive action really well on their own. COUNTIFS is my personal favourite, it is the most flexible option as it can make calculations based on multiple criteria and can be used to answer almost any business query.
As good as the COUNT Functions are on their own. An extra layer of magic is unlocked when they are combined with other FUNCTIONS.
Like this post? You should see my newsletter.
Subscribe to the Excel & ME newsletter to get updates that will empower you to inform your business decisions with data.
