When reviewing an Excel spreadsheet it is important to understand the typical errors people make. Below are ideas on easy ways of finding the SUMIF, COUNTIF, AVERAGEIF errors, in order of riskiness, when auditing an Excel Spreadsheet. Note there are some differences from the SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS which are addressed elsewhere.
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.
Different size of comparison ranges
SUMIF, COUNTIF and AVERAGEIF have a unique risk in that it is possible to say have 10 items in the lookup range but 15 items in the sum range. With SUMIFS et al this is not allowed, but SUMIF does allow it and it effectively just starts from the top again.
So below, once we focus in on the SUMIF functions, you will see that in the first one, the range is row 24 to 36, but the sum range is row 24 to 48 which is different. The second one seems to be more correct in that both ranges refer to row 24 to 48. This is a high risk of an error and should be reviewed for a spreadsheet error.
$ signs on ranges
Similar to VLOOKUP and HLOOKUP, the areas that the SUMIF, COUNTIF and AVERAGEIF 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 in the Formula summary report, the first SUMIF has no $ signs on the ranges and you can see that there are 20 formulas which means it must have copies. Although there are situations where this is purposeful, it is unlikely and therefore a higher risk. Note that the next SUMIF seems to have the absolute references so probably less risky.
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 SUMIF and COUNTIF 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 (e.g. no cell reference but a word like “Annual”) then copies may be incorrect.
So below (1) tells us that there is a slight risk because the criteria is totally relative. (2) also shows risk because this formula has been copied and pasted to many places. (3) reduces the risk because it all seems to be in column D, so copied downward and therefore less risk of an error of looking to the wrong cell.
Special Criteria
SUMIF, COUNTIF and AVERAGEIF 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.
