Excel Tip of the Week: SUMIF

Posted on July 1, 2011 by Doug Midkiff

Today’s topic is the formula SUMIF.

Sumif is a powerful formula that allows you to quickly add values given certain criteria. I know that sentence didn’t make a ton of sense, so let’s just skip to the example. You’ll need the example spreadsheet which you can download here. (SPOLIER ALERT! Next week we will be looking at COUNTIF and using the same spreadsheet, so save this document).

In the spreadsheet there is a list of salespeople and their sales. Each line in the list is a unique sale. What we would like to see is the total sales amount for each salesperson. Using SUMIF we are going to write a single formula that will accomplish our task.

Start by placing your cursor into cell F3 and type:    =SUMIF(
You should see this below where you are typing:

As you can see there are two required inputs in the formula and one optional input (Bonus Tip! anytime a formula input is listed in brackets as you see above, it means the input is optional. Excel will either figure it out for you or it has a default value programmed into the formula)

Range is the range of cells you want evaluated. In our case this is the list of salesman and their sales.
Criteria is the condition or criteria that defines which cells will be added.
[Sum_range] is the actual cells to be added. If omitted, Excel will try to find these cells itself.

Here is the formula you should type in: =SUMIF($B$3:$B$98,E3,$C$3:$C$98). A few things you should notice:

  1. The absolute cell references. Those are important because it allows us to copy the formula to the other cells.
  2. Even though the “sum_range” is optional, I gave the formula an input. I have not found that Excel does a great job figuring out the sum cells on its own. I almost never leave “sum_range” blank.
  3. Remember named ranges? If my list of sales had been named something like, “SalesList” then I could have written the formula this way: =SUMIF(SalesList, E3, $C$3:$C$98)

After you’ve copied the formula to all the salespeople, your final product should look like this:

About Doug Midkiff

I’m really good at Excel. I’m also a Texan, which seems to be a trend among OwenBloggers these days (you can’t stop us, you can only hope to contain us). After graduating from Texas, (Hook’em) I spent four years as a financial analyst before finding my way to Owen where I’m concentrating in finance with an emphasis on real estate. I love my wife, indie coffee shops, disc golf, soccer, web comics, Google maps, urbanism, sustainability, and warm weather.
This entry was posted in Doug Midkiff '12, Excel Tip of the Week. Bookmark the permalink.