Today's question takes us back to Excel 2003. Teresa asks how to do a COUNTIF with 2 conditions in Excel 2003. Today, in Episode #1423, Bill attempts to provide an easy explanation of how SUMPRODUCT actually works. "Learn Excel from MrExcel!"
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1423: COUNTIF multiple conditions in Excel 2003 or earlier.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. Today's question is sent in by Teresa.
Teresa says, “Hey, look. I’ve been watching the podcast trying to learn Excel all by myself and I am up against a roadblock here. I figured out how to use COUNTIF to figure out how many of these records said Annie and that's working great. Now, I need to see how many of these records say Annie and also where the class is B or A.” Now, Teresa is in Excel 2003. They say they're upgrading to excel 2010, but that's going to be a couple months from now and we need the answer now. How do you explain to someone who's relatively new to Excel what the heck SUMPRODUCT does, right? This is absolutely insane.
So, I sent her this formula and I promised that I would, on the podcast, try and explain in layman's terms what the heck is going on with SUMPRODUCT. SUMPRODUCT is used when you have to use COUNTIF or SUMIF with multiple conditions in Excel 2003 or earlier. SUMPRODUCT-- we're going to have two arrays in this case.
Each array is going to test for some conditions.
So, in parentheses, the first array is going to say, hey, go look through all of this stuff over here in column B, B2 to B14-- I'm going to press the F4 key right here. You see, that puts the dollar signs in, and see if that's equal to Annie. That’s kind of like what the COUNTIF is doing. I'm going to press F4 one, two, three times here to freeze it just to column D. So, that's the end of our first array.
Next, times, in parentheses, go look through all of these classes over here in column A-- again press F4, put dollar signs in, is equal to this letter A up here in F1. We'll press F4 just twice to lock it down to the row.
Close parentheses, close parentheses and our formula is done. It will actually give us the right answer.
So, if we go through here, you should have the total number is 13, which is the total number of records. It's working wonderfully, but what is it doing? What is this weird formula doing? We’re going to take a look back here in the formulas. Tab, evaluate formula; that's going to let us watch this formula get calculated in slow motion.
All right, so, the first thing it does is it looks through all of the names in B2 to B14 and sees if they're equal to, in this case, Claire. All right, so, we'll evaluate that and look at this. We get a whole bunch of false false true, so that must mean the third record is true. True false-- so, I'm going to guess just based on this I'm going to see two Claires right there. Two Claires, yes, so those correspond to those trues, a bunch of falses, two more falses then a true.
All right, and so on.
So, that ends up with all these false and trues. Now, I'm going to press evaluate again and we get the same thing. Next, it's going to go look through A2 to A14 and see if that's equal to G1. In this case, G1 is the letter B. So, we'll evaluate. All right, now, we get a bunch of trues and falses. Each time that there's a B there, I get a true. Each time that there's an A there, I get a false and we'll evaluate again.
All right, now, this is where the magic actually happens. It's going to take these two arrays of falses and truths and multiply them together.
That's the product portion of SUMPRODUCT, all right? The way that it works is a false times true or false is false. The only way that you can end up with a true is if you have a true times a true. So, we're going to look for a position where the first array has a true and the second array has it true.
In other words, a row where Claire is in column B, whichever it is we're looking for, B I think is in column A.
So, when I click evaluate, see it all gets reduced down to either zeros or ones. Zero means either array had a false. A one means both arrays had a true in the same spot. So, looking at this one, two, three, four; the fourth item. One, two, three, four; that's the first record where we had Clare and B.
Then there should be one, two zeros and then a true. So, two more zeros and then a true and so on.
Then finally, we're down here down to a single array. That's where the sum takes over. It adds up all of those ones. Evaluate and we get the answer of three. All right, it works wonderfully. It's not as easy to understand as SUMIFS in Excel 2007 or Excel 2010. If you have either of those versions, definitely switch over to the SUMIFS, but if you're stuck back here in Excel 2003 or earlier, this cool function, really hard to understand, will solve your problem.
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 1423: COUNTIF multiple conditions in Excel 2003 or earlier.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen. Today's question is sent in by Teresa.
Teresa says, “Hey, look. I’ve been watching the podcast trying to learn Excel all by myself and I am up against a roadblock here. I figured out how to use COUNTIF to figure out how many of these records said Annie and that's working great. Now, I need to see how many of these records say Annie and also where the class is B or A.” Now, Teresa is in Excel 2003. They say they're upgrading to excel 2010, but that's going to be a couple months from now and we need the answer now. How do you explain to someone who's relatively new to Excel what the heck SUMPRODUCT does, right? This is absolutely insane.
So, I sent her this formula and I promised that I would, on the podcast, try and explain in layman's terms what the heck is going on with SUMPRODUCT. SUMPRODUCT is used when you have to use COUNTIF or SUMIF with multiple conditions in Excel 2003 or earlier. SUMPRODUCT-- we're going to have two arrays in this case.
Each array is going to test for some conditions.
So, in parentheses, the first array is going to say, hey, go look through all of this stuff over here in column B, B2 to B14-- I'm going to press the F4 key right here. You see, that puts the dollar signs in, and see if that's equal to Annie. That’s kind of like what the COUNTIF is doing. I'm going to press F4 one, two, three times here to freeze it just to column D. So, that's the end of our first array.
Next, times, in parentheses, go look through all of these classes over here in column A-- again press F4, put dollar signs in, is equal to this letter A up here in F1. We'll press F4 just twice to lock it down to the row.
Close parentheses, close parentheses and our formula is done. It will actually give us the right answer.
So, if we go through here, you should have the total number is 13, which is the total number of records. It's working wonderfully, but what is it doing? What is this weird formula doing? We’re going to take a look back here in the formulas. Tab, evaluate formula; that's going to let us watch this formula get calculated in slow motion.
All right, so, the first thing it does is it looks through all of the names in B2 to B14 and sees if they're equal to, in this case, Claire. All right, so, we'll evaluate that and look at this. We get a whole bunch of false false true, so that must mean the third record is true. True false-- so, I'm going to guess just based on this I'm going to see two Claires right there. Two Claires, yes, so those correspond to those trues, a bunch of falses, two more falses then a true.
All right, and so on.
So, that ends up with all these false and trues. Now, I'm going to press evaluate again and we get the same thing. Next, it's going to go look through A2 to A14 and see if that's equal to G1. In this case, G1 is the letter B. So, we'll evaluate. All right, now, we get a bunch of trues and falses. Each time that there's a B there, I get a true. Each time that there's an A there, I get a false and we'll evaluate again.
All right, now, this is where the magic actually happens. It's going to take these two arrays of falses and truths and multiply them together.
That's the product portion of SUMPRODUCT, all right? The way that it works is a false times true or false is false. The only way that you can end up with a true is if you have a true times a true. So, we're going to look for a position where the first array has a true and the second array has it true.
In other words, a row where Claire is in column B, whichever it is we're looking for, B I think is in column A.
So, when I click evaluate, see it all gets reduced down to either zeros or ones. Zero means either array had a false. A one means both arrays had a true in the same spot. So, looking at this one, two, three, four; the fourth item. One, two, three, four; that's the first record where we had Clare and B.
Then there should be one, two zeros and then a true. So, two more zeros and then a true and so on.
Then finally, we're down here down to a single array. That's where the sum takes over. It adds up all of those ones. Evaluate and we get the answer of three. All right, it works wonderfully. It's not as easy to understand as SUMIFS in Excel 2007 or Excel 2010. If you have either of those versions, definitely switch over to the SUMIFS, but if you're stuck back here in Excel 2003 or earlier, this cool function, really hard to understand, will solve your problem.
Well, hey, I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
