#5 of the Top 5 Excel Functions to Know: SUMIFS

SUMIFS the 5th of the Top 5 Excel Functions to Know

And here we are at the 5th of the Top 5 Excel Functions to Know!

SUMIFS is in the Top 5 because when you’re working with numbers and need to do analysis or summaries, SUMIFS makes it so easy to see details that you need to see.

We didn’t always have this. SUMIFS was a welcome addition in Excel 2007 because prior to 2007, SUMIF (singular) existed but was limited. Beyond  SUMIF, Excel users would create a tricky formula using SUMPRODUCT. It wasn’t fun.

SUMIFS: What & Why

SUMIFS allows you to sum numbers based on multiple criteria Let’s build up to an example of why we’d use SUMIFS:

  • First, you want a simple sum; e.g., total of outstanding checks.
    • Just add them up in a column or row.
  • Now, you want all outstanding checks that have been outstanding more than 10 days.
    • You use SUMIF for that. You have one criteria: checks that were written more than 10 days ago.
  • Now, let’s sum the outstanding checks that were written more than 10 days ago AND for amounts over $50
    • This is where you taste the full flavor of SUMIFS! Two Criteria.
    • You can add up to 127 criteria

Five more examples:

SUMIFS Examples

WARNING SUMIFS is not backward compatible with Excel 2003.



Translated into English, SUMIFS asks:

  • What do you want me to sum?
  • Where will I look for the first criteria?
  • What is the first criteria?
  • Where will I look for the second criteria?
  • What is the second criteria?
  • … (this pattern repeats for up to 127 pairs of criteria)


That’s it!

SUMIFS is the 5th and last in the series of The Top 5 Excel Functions to Know. Thank you to the students who attended an Excel Demo. You demanded the list, and here it is. I hope this has been useful. It’s been fun and interesting to put this on.

Now go on out there and keep your data clean!

As always, you are invited to send question to me for a response on this very blog.

Share Button

Oz du Soleil

Oz du Soleil is a Microsoft Excel MVP. He's an Excel Trainer whose courses have been described as fun and they get people to relax about using Excel. Oz is a U.S. Navy Veteran, and loves good bourbon, and spicy food. Above everything else, Oz's commitment is to clean data.

Get every new post delivered to your Inbox

Join other followers: