MrExcel Publishing
Your One Stop for Excel Tips & Solutions


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 Function
SUMIF Function

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.

SUMIFS Function
SUMIFS Function

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.

Watch Video

  • 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.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2043 some ifs all right i am podcasting
  • out all of my tips from this book click
  • the eye on the top right hand corner to
  • get to the playlist to all of these
  • videos back in the old old days excel to
  • 1996-97 they introduced two great
  • functions called some if and count if
  • here's some if some of says go look
  • through the range of rep names over
  • there and column B I'll press f4 see if
  • it's equal to Allen matts comma and if
  • it is add up the corresponding cell from
  • column D all right now hey in the past I
  • might have noted that you could just put
  • D to there and it will automatically
  • make the shape be the same but that was
  • bad advice because when you do that this
  • form that becomes a volatile and that's
  • a bad thing you never want to have
  • volatile formulas alright so some if
  • looks through alamat said 1543 units and
  • when we copy that down we can see what
  • each person this is great they had some
  • if and then count if count if 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 some product some product and I'm
  • glad I don't have to explain some
  • product at least not for this because
  • now in Excel 2007 they gave us some ifs
  • some obsess where we want to see how
  • many units Alan math sold of widgets so
  • we have to check both column B and
  • column C so the plural version some ifs
  • equals some ifs and really you can start
  • using some of us for everything because
  • it's very versatile it'll handle one
  • condition or 126 conditions hundred
  • twenty seven kids in something like that
  • okay 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 one is look at all the
  • reps over in column B I'll press f4 and
  • see if that's equal to Allen mats now I
  • need to be able to copy this to the
  • right so on a press f4 one two three
  • times that puts a single dollar sign
  • before the f
  • the four is allowed to change but the
  • apps can be locked down comma next
  • condition go look through all the the
  • product names over in column C F for see
  • if that's equal to widget in this case I
  • press f4 12 times lock it down to just
  • the row alright so Allen Matt's 1290
  • widgets and 253 gadgets double-click to
  • shoot that down not far to shoot down I
  • know and we can see for each of these
  • people how many widgets how many gadgets
  • all right so some ifs new in Excel 2007
  • here's the overview excel 97 some if and
  • countif Excel 2007 introduce some ifs
  • introduced countifs it's shortened some
  • if / countif into average if and then
  • gave us the plural version of that
  • average ifs then several years later
  • Excel 2016 not all versions you have to
  • be an office 365 and the februari 2016
  • release they added max ifs min ifs and
  • ifs note here they no longer feel
  • compelled to give us max if and min if
  • and I agree with that because max ifs
  • will do both all right excel v-necks 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 Roman ifs and Arabic ifs
  • okay 40 greatest excel tips of all time
  • plus another 30 bonus tips for excels
  • birthday all of them all of these
  • podcast episodes going back to what
  • oddest first everything we've talked
  • about since August first in this one
  • book 25 bucks in print $10 an e-book
  • click the I in the top right hand corner
  • and you can get to those all right
  • episode recap some if and count if have
  • been around since excel 97 they do not
  • handle multiple conditions while you had
  • to use some product but then they added
  • plural version some ifs and Countess
  • they also shorten some if / count if
  • into average if great and at gave us
  • average ifs then just recently in
  • February 2016 they gave us max ifs min
  • ifs and the ifs function there's a video
  • on the three of those i'll put a link
  • right there yep up in that I there still
  • though no Romanovs now you know I asked
  • people what their favorite excel tips
  • were and make the appt sure 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 neck cast from MrExcel
  • you're still here alright I think Roman
  • is one of the stupidest functions and
  • all excel though it was the birth of
  • this book mr. XL XL right my 40th book
  • but frankly they really could do a Roman
  • ifs all right here's how it would work
  • equal Roman ifs you add up with these
  • Roman numerals here because it's the
  • plural version you have to put those in
  • first f 4 comma and then look through
  • these regions f4 and see if it's equal
  • to east and there it is right that Plus
  • that Plus that is 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 File

Download the sample file here: Podcast2043.xlsm

Title Photo: aitoff / Pixabay