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.
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.
Transcript of the video:
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!
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!
