Login

  • Jun 3

The only time to use SUMIF

I no longer teach the SUMIF function. I teach the SUMIFS function as it provides more solutions because it handles multiple criteria. The two functions differ in their argument sequence which can be confusing when switching between them. Rather than learning both, it is easier to learn the SUMIFS function. There is however one time when the SUMIF function is shorter and easier to use than SUMIFS.

Sign up to hear about free Excel training.

I won't share your email with anyone.

  • 1 min read

SUMIF Secret

Did you know the third argument in the SUMIF function is optional? If you omit the third argument, Excel adds up the criteria range.

When the range to sum is also the criteria range, e.g. you want to add up all the positive numbers in a range, then you only need two arguments.

In the image below a basic Trial Balance shows debits as positive and credits as negative.

The formulas for debits (positives) and credits (negatives) are shown below.

Debits formula

=SUMIF($B$2:$B$9,">0")

Credits formula

=SUMIF($B$2:$B$9,"<0")

The SUMIFS function version requires all three arguments – see image below.

If your criteria range and range to sum are the same, the SUMIF function gives you a shorter and cleaner formula.

In most other cases, the SUMIFS function remains the better solution.

0 comments

Joinor login to leave a comment