October 06, 2017 - by Bill Jelen
SUMIF and COUNTIF were great when they were introduced in Excel 97. But they could only handle one condition. The new, superior SUMIFS handles up to 127 conditions.
Did you notice the “S” that got added to the end of SUMIF starting in Excel 2007? While SUMIF and SUMIFS sound the same, the new SUMIFS can run circles around its elder sibling.
The old SUMIF and COUNTIF have been around since Excel 97. In the figure below, the formula tells Excel to look through the names in B2:B22. If a name is equal to the name in F4, then sum the corresponding cell from the range starting in D2. (I realize that most people would use $D$2:$D$22 as the last argument, but you only have to specify the top cell.)
SUMIF and COUNTIF were great when you only had one condition. But if you had two or more things to check, you had to switch over to SUMPRODUCT. (I realize most people would replace my multiplication signs with commas and add a double-minus before the first two terms, but mine works, too.)
SUMIFS allows for up to 127 conditions. Because you might have an indeterminate number of conditions in the function, the numbers that you are adding up move from the third argument to the first argument. In the following formula, you are summing D2:D22, but only the rows where column B is Allen Matz and column C is Widget.
Excel 2007 also added plural versions of COUNTIFS and AVERAGEIFS. All of these "S" functions are very efficient and fast.
Thanks to Nathi Njoko, Abshir Osman, Scott Russell, and Ryan Sitoy.
- SUMIF and COUNTIF have been around since Excel 97
- They did not handle multiple conditions well - you had to use SUMPRODUCT
- Starting in Excel 2007, the Excel team added plural versions: SUMIFS, COUNTIFS
- They also shortened SUMIF()/COUNTIF into AVERAGEIF
- And they added AVERAGEIFS
- In the Feb 2016 release of Office 365, they added MAXIFS, MINIFS, IFS
- But still no ROMANIFS
- Thanks to Nathi Njoko, Abshir Osman, Scott Russell, and Ryan Sitoy.
- Excel Guru Mission Patch if you can figure out how ROMANIFS is working at the end.
Learn Excel from MrExcel podcast, episode 2043 – SUMIFS!
Alright, I am podcasting out all of my tips from this book, click the “i” on the top-right hand corner to get to the playlist to all of these videos!
Back in the old, old days of Excel 97, they introduced two great functions called SUMIF and COUNTIF, here's SUMIF. SUMIF says “Go look through the range of rep names over there in column B,” I'll press F4, “see if it's equal to Allan Matz, comma, and if it is, add up the corresponding cell from column D!” Alright, now hey, in the past, I might have noted that you could just put D2 there, and it will automatically make the shape be the same. But that was bad advice, because when you do that, this formula becomes volatile, and that's a bad thing, you never want to have volatile formulas, alright. So SUMIF looks through Allan Matz, found 1543 units, and when we copy that down, we can see what each person did. This is great, they had SUMIF and then COUNTIF. COUNTIF would tell us how many orders there were for each of those people, but this became a massive problem, when we had two or more conditions. You had to switch over to using SUMPRODUCT, and I'm glad I don't have to explain SUMPRODUCT, at least not for this.
Because now in Excel 2007, they gave us SUMIFS, that’s where we want to see how many units Allan Matz sold of Widgets, so we have to check both column B and column C. So the plural version, =SUMIFS, and really you can start using SUMIFS for everything, because it's very versatile, it'll handle one condition, or 126-127 conditions, or something like that. OK now, here's the weird thing, usually the sum range was the last thing we specified, they reverse that and it's the first thing we specify. Alright, so that's what we're going to add up, and then criteria range and criteria. So the criteria range 1 is look at all the reps over in column B, I'll press F4 and see if that's =Allan Matz.
Now, I need to be able to copy this to the right, so I want to press F4 3 times, that puts a single $ before the F, the 4 is allowed to change, but the F’s going to be locked down, comma. Next condition, go look through all the product names over in column C, F4, see if that's equal to Widget, in this case I press F4 2 times lock it down to just the row. Alright, so Allan Matz, 1290 widgets and 253 gadgets, double-click to shoot that down, and we can see for each of these people how many Widgets, how many Gadgets, alright.
So SUMIFS, new in Excel 2007, here's the overview. Excel 97, SUMIF and COUNTIF. Excel 2007 introduce SUMIFS, and introduced COUNTIFS, it's shortened SUMIF/COUNTIF into AVERAGEIF, and then gave us the plural version of that, AVERAGEIFS. Then several years later, Excel 2016, not all versions, you have to be an Office 365 and the February 2016 release, they added MAXIFS, MINIFS, and IFS. Note here, they no longer feel compelled to give us MAXIF and MINIF, and I agree with that, because MAXIFS will do both, alright. Excel vNext, or maybe we can just, you know, look at the, it'll be about Excel 2026, they'll probably add another batch of these, and I'm hoping for ROMANIFS and ARABICIFS!
OK, 40, greatest Excel tips of all time, plus another 30 bonus tips for Excel’s birthday, all of them, all of these podcast episodes, going back to what, August 1st! Everything we've talked about since August 1st in this one book, $25 in print, $10 an e-book, click the “i” in the top-right hand corner, and you can get to those. Alright, episode recap: SUMIF and COUNTIF have been around since Excel 97, they do not handle multiple conditions well, you had to use SUMPRODUCT. But then they added plural version, SUMIFS and COUNTIFS, they also shorten SUMIF/COUNTIF into AVERAGEIF, great, and then gave us AVERAGEIFS. Then, just recently in February 2016, they gave us MAXIFS, MINIFS, and the IFS function, there's a video on the 3 of those, I'll put a link right there, yep, up in that “i” there, still though, no ROMANIFS. Now you know, I asked people what their favorite Excel tips were, and Nathi, Abshir, Scott, and Ryan all set this in as their favorite tip.
So thanks to them, and thanks to YOU for stopping by, we'll see you next time for another netcast from MrExcel!
You're still here… Alright, I think ROMAN is one of the stupidest functions in all Excel, though it was the birth of this book, “MrExcel XL”! Right, my 40th book, but frankly they really could do a ROMANIFS! Alright, here's how it would work, =ROMANIFS, you add up with these Roman numerals here, because it's the plural version you have to put those in first. F4, comma, and then look through these regions, F4, and see if it's equal to East, and there it is, right? That+that+that=that! Hey, I've got an Excel guru mission patch for the first person on YouTube - who can tell me how I did that? it wasn't VBA!
Download the sample file here: Podcast2043.xlsm
Title Photo: aitoff / Pixabay