When reviewing an Excel spreadsheet it is important to understand the typical errors people make. Below are ideas on easy ways of finding the SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS errors, in order of riskiness, when auditing an Excel Spreadsheet.
It is useful to have an add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.
$ signs on ranges
Similar to VLOOKUP and HLOOKUP, the areas that the SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS and MINIFS look at normally stay the same, so you would expect to see $ signs on both rows and columns making these ranges absolute.
As shown below when the SUMIFS are listed in a single report, (1) and (2) have no $ signs included and (3) and (4) only have $ signs at the end of the ranges. To use these would be risky and need a quick investigation. (5) is more like what we would expect with $ signs on the rows and columns of all the ranges.
Where should it be looking
Although you will probably do this when reviewing the formula properly (keep in mind in this section we are looking for quick wins), the criteria used needs to be considered. Unlike a VLOOKUP where you will get NA error messages, with SUMIFS and COUNTIFS et al, if it is looking at the wrong cell because you have the incorrect $ signs, it will just give a total of 0, so you may not spot it. Similarly if the criteria has been hardcoded then copies may be incorrect.
So below, instead of referencing a cell, the text is ‘hardcoded’ into the formula. This is a risk especially as it seems that these formula are copied 20 times each. Similarly, if it had been a reference e.g. cell G10, you would need to consider if it needed $ signs to get the correct criteria.
Special Criteria
SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS, MINIFS allow you to, instead of having static criteria, you can include a logic check like “>0”. These are slightly more risky with the same risks as mentioned in the IF’s i.e. should it perhaps be >=0 or just >0?
As shown below, when it is listed for you using the Excel Analyzer add in, it is easier to find and consider.
