Use countif for variable value.
A question from Trinidad about using countif. Episode #1175 shows you how.
A question from Trinidad about using countif. Episode #1175 shows you how.
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
First of all, I want to thank everyone for your patience.
You know, the podcasts have been few and far between, we're down to the final five weeks here, where I have to finish five books about Excel 2010.
Two books are done, thought I’d stop in the office and record some more podcasts, so.
I’m not going to get in five-a-week, I talked to Mike Girvin, we're going to restart the Dueling Excel podcasts again after February 10th, so watch for those.
Still try and get you a few podcasts each week, though, and this question came in from Trinidad, the country Trinidad, I did a seminar there a few years ago, could be going back in June.
Hey, convince your boss he should go to an Excel seminar in Trinidad, great place to be.
Someone's trying to use COUNTIF, but COUNTIF for sorting a variable range over here.
So the formula they were trying was COUNTIF, look at all these cells and see if it's “<&A2”.
OK, well that is so close.
I said “You almost had it.” What we have to do is we have to concatenate two things, we have to take the, not greater than, the less than sign, and put that in quotes, just that, and then say &A2 and get rid of that quote.
Then what happens is, when Excel evaluates that formula, they take the number in A2, and they concatenate it together with the < sign, and then it works.
So there we have seven months that were under this rate, over here in column A, and copy it down, and you see, that works.
Let's just take a quick look at Evaluate Formula, and I'll choose this top one, Evaluate Formula.
See, the very first thing that happens is A2 gets evaluated, that's 0.45, and then smashes that whole thing together, and THAT's how we get the criteria range.
So there you go, want to thank you for stopping, by we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
First of all, I want to thank everyone for your patience.
You know, the podcasts have been few and far between, we're down to the final five weeks here, where I have to finish five books about Excel 2010.
Two books are done, thought I’d stop in the office and record some more podcasts, so.
I’m not going to get in five-a-week, I talked to Mike Girvin, we're going to restart the Dueling Excel podcasts again after February 10th, so watch for those.
Still try and get you a few podcasts each week, though, and this question came in from Trinidad, the country Trinidad, I did a seminar there a few years ago, could be going back in June.
Hey, convince your boss he should go to an Excel seminar in Trinidad, great place to be.
Someone's trying to use COUNTIF, but COUNTIF for sorting a variable range over here.
So the formula they were trying was COUNTIF, look at all these cells and see if it's “<&A2”.
OK, well that is so close.
I said “You almost had it.” What we have to do is we have to concatenate two things, we have to take the, not greater than, the less than sign, and put that in quotes, just that, and then say &A2 and get rid of that quote.
Then what happens is, when Excel evaluates that formula, they take the number in A2, and they concatenate it together with the < sign, and then it works.
So there we have seven months that were under this rate, over here in column A, and copy it down, and you see, that works.
Let's just take a quick look at Evaluate Formula, and I'll choose this top one, Evaluate Formula.
See, the very first thing that happens is A2 gets evaluated, that's 0.45, and then smashes that whole thing together, and THAT's how we get the criteria range.
So there you go, want to thank you for stopping, by we'll see you next time for another netcast from MrExcel!
