The Question: "I have a =COUNTIF to count values less than 10 and another to count values less than 20; but how do I count the values that fall between 10 and 20?" In 2007 and newer, there is a New Function called '=COUNTIFS". Follow along with Bill in Episode #1508 to learn how to solve this question - even if you are using Excel 2003 or earlier!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This episode is the video podcast companion to the book, "Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel From MrExcel, Podcast Episode 1508: Count Between A Range.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Today's question is sent in…I'm sorry I forget by whom.
So, they had a whole series of numbers over here in column A and we're able to use COUNTIF to count all of the values < 10 and also able to use COUNTIF to count all the numbers < 20, alright, but then, the person said, alright, I'm having a real tough time combining these and counting everything that's between 10 and 20.
How do you put both of those into a COUNTIF, and I said, alright, well, good news.
If you have Excel 2007 or newer, there's a new function called COUNTIFS, get it?
It's like COUNTIF plural, COUNTIFS, and, in that case, you can check for 2 conditions, 3 conditions, 4 conditions, 5 conditions, whatever you want.
In this case, the criteria range is always going to be the same, so it's going to be A2 to A200 , and we want in “ > or = 10.
So, that's the first pair of criteria.
Next pair of criteria, come back here to the exact same range, press F4 again, and, in “ , < 20, alright, and COUNTIFS will count the values between those 2 ranges.
[ =COUNTIFS($A$2:$A$200,“>=10”,$A$20:$A$200,“<20”) ] The problem is this is not going to work if you have Excel 2003 or older.
Now, the file that they had sent was stored as .xlsm so I knew that they at least had a way to save Excel 2007 files, so I'm going to guess that that's going to work.
Unfortunately, if you have Excel 2003, then you're going to have to go off and use SUMPRODUCT or DCOUNT or even, you know, just simply, you know, it's this – that, right?
So, I said it's kind of I feel like I'm cheating there, basically taking that formula, CONTROL+C, and putting it, CONTROL+V, - something like that to come up with the same answer as before.
[ =COUNTIFS($A$2:$A$200,D1)-COUNTIFS($A$2:$A$200,C1) ] So, COUNTIFS definitely the better way to go.
If you're stuck back in until 2003 or earlier, you're going to be doing a couple of COUNTIFS…or a single COUNTIF and another kind of subtracting the two.
Okay.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
Learn Excel From MrExcel, Podcast Episode 1508: Count Between A Range.
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Today's question is sent in…I'm sorry I forget by whom.
So, they had a whole series of numbers over here in column A and we're able to use COUNTIF to count all of the values < 10 and also able to use COUNTIF to count all the numbers < 20, alright, but then, the person said, alright, I'm having a real tough time combining these and counting everything that's between 10 and 20.
How do you put both of those into a COUNTIF, and I said, alright, well, good news.
If you have Excel 2007 or newer, there's a new function called COUNTIFS, get it?
It's like COUNTIF plural, COUNTIFS, and, in that case, you can check for 2 conditions, 3 conditions, 4 conditions, 5 conditions, whatever you want.
In this case, the criteria range is always going to be the same, so it's going to be A2 to A200 , and we want in “ > or = 10.
So, that's the first pair of criteria.
Next pair of criteria, come back here to the exact same range, press F4 again, and, in “ , < 20, alright, and COUNTIFS will count the values between those 2 ranges.
[ =COUNTIFS($A$2:$A$200,“>=10”,$A$20:$A$200,“<20”) ] The problem is this is not going to work if you have Excel 2003 or older.
Now, the file that they had sent was stored as .xlsm so I knew that they at least had a way to save Excel 2007 files, so I'm going to guess that that's going to work.
Unfortunately, if you have Excel 2003, then you're going to have to go off and use SUMPRODUCT or DCOUNT or even, you know, just simply, you know, it's this – that, right?
So, I said it's kind of I feel like I'm cheating there, basically taking that formula, CONTROL+C, and putting it, CONTROL+V, - something like that to come up with the same answer as before.
[ =COUNTIFS($A$2:$A$200,D1)-COUNTIFS($A$2:$A$200,C1) ] So, COUNTIFS definitely the better way to go.
If you're stuck back in until 2003 or earlier, you're going to be doing a couple of COUNTIFS…or a single COUNTIF and another kind of subtracting the two.
Okay.
Well, hey.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
