Using SUMIFS for long-range planning cost assumptions

Excel's SUMIFS formula is a simple but powerful function that any model-builder should understand. The logic is simple. The first argument is the range that the user wants to be added. The next argument pair is the criteria range and the accepted criterion required to add the value in the sum range.

Budgets and long-range plans are a great opportunity to use the SUMIFS formula. A data model I have succeeded with uses a tab for listing all the model assumptions and a second tab with a SUMIFS adding each unique intersection of assumption criterion. The SUMIFS formula calculates the total cost for each unique intersection.

Of course, there are a few positives and negatives when using the SUMIFS formula in models:

POSITIVE: The SUMIFS formula is simple to understand - If your model is going to be shared, the SUMIFS formula is easy to understand. I've been burned by over-engineering Excel models in the past. But the SUMIFS formula is simple enough for anyone to understand.
POSITIVE: SUMIFS give you flexibility with different assumption criterion - The SUMIFS formula accepts multiple criterion associations, giving flexibility on different evaluation criteria. This requires some balance. Models must be complex enough to paint an accurate picture but simple enough for someone else to follow along. This leads me to my next point...
NEGATIVE: The more unique intersections, the more cumbersome the model becomes - Let's assume that Company A has 40 GL accounts, 12 departments, and five programs. The total possible unique intersections would be 2,400 (40 x 12 x 5). If you wanted to add a fourth criterion with five assumptions, now your unique combinations become 12,000. This will slow down your model. The workaround is to keep only the selected combinations using the Remove Duplicates feature, but users must remember to update this each time assumptions are added.
NEGATIVE: The assumption criterion needs to be an exact match; otherwise, the formula won't work - The SUMIFS formula criterion must match the assumption criterion. Excel doesn't give you a warning that the formula is failing. The returned value from the SUMIFS will simply be a zero. The model must have several quality checks to ensure everything matches (this is a good principle even if you aren't using a SUMIFS).

The SUMIFS is a powerful formula for building models. However, its limitations will make it challenging to maintain and collaborate. The maintenance required is why 88% of spreadsheets contain errors.

Sign up for my blog if you are an accounting team looking for more tips and tricks related to your data. Or, if you are a NetSuite user, consider signing up for my saved search course for accountants.