SUMIF or SUMIFS

The SUMIF function allows you to sum values in a given range, provided the corresponding cells in another range satisfy a chosen criterion. The SUMIFS function on the other hand, performs the same operation, but allows you specify multiple criteria.

Let’s recap the syntax:

SUMIF(range, criteria, sum_range)

SUMIFS(sum_range, criteria_range1, criteria1,…)

You might think that you should choose between the two based on how complex your criteria are, but you can build flexibility into your modelling if you use the SUMIFS function exclusively in these situations.

The reasoning behind this, is that by setting up with the SUMIFS function, you can always add additional criteria in later on; whereas, with the SUMIF function, you need to tweak the syntax. This is because the SUMIFS function’s first argument is the sum range, while in the SUMIF, it is the final one.

So to avoid moving things around, always use the SUMIFS. This approach really pays dividends when an existing model needs expanding. Overlays are always easier to build if the existing formulae are flexible.

Now you know whether you need SUMIF or SUMIFS, If you would like some guidance or training on your Excel financial modelling, contact us today at info@pps.financial.