The SUM Function in Excel vs. SUMIF and SUMIFS

The SUM function in Excel provides a quick and convenient way to calculate a total for a row or column of numbers. Users do this so frequently that Microsoft has included the AutoSum button on Excel’s Ribbon in two different places!

You can find the AutoSum button in these locations:

  • On the Home tab in the Editing group.
  • On the Formulas tab in the Function Library group.

Like most of the features in Excel, there is more than one way to use the SUM function.

Related: 7 Excel Functions That Will Make Your Life Easier

The Excel Sum Function in Action

Let’s examine how the SUM function works. There are a couple of methods you can use.

SUM Function Example

You can type the sum function. In the example (above), if you wanted to add up the column of numbers under Amount, you could:

  1. Click in cell D12.
  2. Type =SUM(D4:D11).
  3. Press ENTER.

Or, if you’d rather not type the sum function, you can click the AutoSum button instead. Using AutoSum, you could:

  1. Click in cell D12.
  2. Click the AutoSum button in the Home > Editing group.
    Excel automatically highlights the range D4:D11.
  3. Click the AutoSum button again to confirm or press ENTER.

Limitations of the SUM Function

Most people find the SUM function very useful but there are things it won’t do. Using the Toronto Technology Partners spreadsheet example (above), how would we analyze the revenue this company made from training?

We could sort the list according to the services provided and carefully add up the entries related to training but that would be cumbersome and much more difficult if the list of transactions was longer.

So what is an Excel user to do? You can use the SUMIF function!

The SUMIF Function

The SUMIF function will calculate a running total from a series of values, if entries meet a condition that you specify. Here is the syntax for the SUMIF function:

SUMIF Syntax

Like every function in Excel, the SUMIF function starts with an equals sign (=), followed by the function name and a series of arguments inside parentheses.

The SUMIF function accepts 3 arguments:

  • range – the range of cells you’ll evaluate, or compare, with your criteria.
  • criteria – contains the number or expression you’ll use as a condition to evaluate each entry in ‘range’.
  • sum_range – the numbers that will be added to a running total, if an entry in ‘range’ meets your criteria.

Let’s return to our example.

SUMIF Function Example

To calculate total training revenue, you would:

  1. Click in cell C15.
  2. Type =SUMIF(B4:B11,B15,D4:D11).
  3. Press ENTER.

In this example, B4:B11 represents the range of cells we’re examining. B15, our criteria, refers to a cell containing the word ‘Training’. D4:D11 are values we will add to a running total, for rows matching our criteria.

Limitations of the SUMIF Function

The SUMIF function is very powerful but you’re limited to evaluating a single condition. Thinking about our example, you can calculate revenue for a given service or location but not both. What if we need to determine total training revenue booked in Toronto? You could use the SUMIFS function!

The SUMIFS Function

The SUMIFS function will calculate a total from a series of values – and it lets you evaluate multiple conditions.

Here is the syntax for the SUMIFS function:

SUMIFS Syntax

The SUMIF function accepts multiple arguments:

  • sum_range – numbers that will be added to a running total, if values in criteria_range1 match our criteria.
  • criteria_range1 – the range of cells being evaluated by criteria1.
  • criteria1 – the number or expression used to determine whether values in criteria_range1 match our first condition.

Let’s look at one more example.

SUMIFS Function Example

To calculate a total for training related to training in the Toronto area, you would:

  1. Click in cell C15.
  2. Type =SUMIFS(D4:D11,B4:B11,B15,C4:C11,B16).
  3. Press ENTER.

In this example D4:D11 represents the series of numbers we’ll add up, if our conditions are met. B4:B11 is the range used to identify services being offered. B15 stipulates that the service we’re interested in is ‘Training’. C4:C11 is the range used to identify the location where services were provided. B16 indicates that we’re only interested in services provided in ‘Toronto’. The total revenue for training provided in Toronto is $3,000.

Do You Use These Functions?

Have you used SUM, SUMIF, or SUMIFS? The first time I encountered SUMIFS, I thought it was a typo! Let me know what you think these functions – or tell me if there’s another topic you’d like to me to write about – in the comments below!