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:
WARNING SUMIFS is not backward compatible with Excel 2003.
SYNTAX
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)
SCREENCAST DEMONSTRATION
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.
