Excel Tip of the Week: SUMIFS & COUNTIFS

Posted on April 20, 2012 by Doug Midkiff

Today’s topic is SUMIFS and COUNTIFS.

You might be thinking, “Wait a minute Doug, you already covered this topic!” But look again at those formula names, there is an “S” at the end of them indicating a completely different and more advanced function.

These two formulas are actually new to Excel as of 2007. These formulas have helped reduce the need for moving data into Microsoft Access or coming up with super complex array formulas. As their names might suggest these functions allow the user to sum or count data based on multiple criteria instead of just a single criteria.

Here are the inputs for the SUMIFS function:

Notice how “sum_range” is the first input in this for formula which is the opposite of the SUMIF function. Also notice the ellipsis at the end which indicates that more inputs can be received. This formula can actually handle up to 127 different range/criteria pairs.

Here are the inputs for COUNTIFS:

COUNTIFS is very similar to SUMIFS just without the “sum_range.” This formula can also hold up to 127 different range/criteria pairs.

Let’s look at an example. Download the example spreadsheet here.

The scenario for this example is that you have a list of all the sales for your five sales people and you would like to know what their sales in the Southwest region look like. Thus, you are trying to find the total amount of sales based on two criteria: 1) the salesperson 2) the region. With the regular SUMIF or COUNTIF function you can only specify one criterion, thus we will use SUMIFS and COUNTIFS.

  1. Once you have the spreadsheet open, navigate to cell I3 and copy this formula into the cell: =SUMIFS($D$3:$D$52,$B$3:$B$52,G3,$C$3:$C$52,H3).
  2. Copy the formula down to the other sales people
  3. Navigate to cell J3 and copy in this formula: =COUNTIFS($B$3:$B$52,G3,$C$3:$C$52,H3)
  4. Copy the formula down to the other sales people.
  5. Good job.

Since these formulas are a bit complex I’ve made some diagrams to help explain what is going on:

 

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 and tagged , , , , . Bookmark the permalink.

One Response to Excel Tip of the Week: SUMIFS & COUNTIFS

  1. Rob Collie says:
    May 4, 2012 at 2:47 pm

    Hi Doug, I saw this article on an aggregator site and since it was Vanderbilt and Excel I had to click through – I graduated from Vandy Engineering (Computer Science) in ’96 and went straight to Microsoft, where I ultimately worked on Excel 2003, Excel 2007, and then lastly and most importantly… PowerPivot.

    Is PowerPivot even on the radar at biz schools yet? I promise you it’s the biggest advancement in Excel in 10+ years, as big as the introduction of pivots themselves. I left MS 2+ years ago, near the end of my work on PowerPivot, and “went pro” with it. My entire career is built around PowerPivot now, as is my startup.

    Quick summary of what it is: http://www.powerpivotpro.com/what-is-powerpivot/

    Drop me a note, would love to hear from you. Even if you’re from Texas :P (For some reason, TX folks annoyed me when I was at Vandy but in my life since then, I’ve discovered it’s a candidate for the best state in the union).

    Say hi to SATCO for me.

    -rob

    Reply

Leave a Reply