Kyle is trying to build a worksheet to create practice SAT tests for his students. His IF formula to mark answers as correct is working fine, but the COUNT function cant seem to count the correct answers. Episode 534 troubleshoots this function.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen, today's question comes from Kyle in California.
If you have a question for the podcast send it in.
We will get to you on a future podcast.
Kyle setting up a workbook for his students to use when taking practice ACT test or SAT tests, and basically he allows the students to enter their answer in COLUMN B and he has hidden in COLUMN C the correct answer.
So his formula over here in COLUMN D is a fairly straightforward if statement says =IF(B2=C2,’’X’’,’’’’).
In other words, if the student got the answer right then he puts an X otherwise he puts QUOTE QUOTE he puts nothing, and everything was working great till Kyle came down to the end and used the count function to try and count how many Xs there are.
Well you know the first problem we have is COUNT only counts numeric values it doesn't count text values, and so then you might try and use the COUNTA function, COUNTA is used for counting either numeric or text.
Unfortunately when we use COUNTA, all of those QUOTE QUOTES get counted as something.
So that doesn't work either.
I told Kyle that we have to start using The COUNTIF function, we're going to look through the range of Xs or blanks and count how many are equal to in quotes uppercase X =COUNTIF(D2:D11 ,’’X’’), and sure enough that works.
We have five, if someone gets the right answer here in question number 9 it increases to six, so that's one solution.
The other solution that I suggested was that instead of using an X and a blank you just simply use a 1 and a blank =IF(B2=C4, 1 ,’’’’). Copy that down to all of our cells and then once the ones or blanks are there, it's very easy just to use the SUM function to go through and sum up all of those 1 values =SUM(D2:D11).
So if you don't care about presentation you're just trying to get the total score using the 1 is the way to go.
Otherwise COUNTIF, great little function that we have and there's also a SUMMIT function that will talk about some of the days.
Thanks to Kyle for sending in that great question.
If you have a question please feel free to drop us a line, otherwise we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen, today's question comes from Kyle in California.
If you have a question for the podcast send it in.
We will get to you on a future podcast.
Kyle setting up a workbook for his students to use when taking practice ACT test or SAT tests, and basically he allows the students to enter their answer in COLUMN B and he has hidden in COLUMN C the correct answer.
So his formula over here in COLUMN D is a fairly straightforward if statement says =IF(B2=C2,’’X’’,’’’’).
In other words, if the student got the answer right then he puts an X otherwise he puts QUOTE QUOTE he puts nothing, and everything was working great till Kyle came down to the end and used the count function to try and count how many Xs there are.
Well you know the first problem we have is COUNT only counts numeric values it doesn't count text values, and so then you might try and use the COUNTA function, COUNTA is used for counting either numeric or text.
Unfortunately when we use COUNTA, all of those QUOTE QUOTES get counted as something.
So that doesn't work either.
I told Kyle that we have to start using The COUNTIF function, we're going to look through the range of Xs or blanks and count how many are equal to in quotes uppercase X =COUNTIF(D2:D11 ,’’X’’), and sure enough that works.
We have five, if someone gets the right answer here in question number 9 it increases to six, so that's one solution.
The other solution that I suggested was that instead of using an X and a blank you just simply use a 1 and a blank =IF(B2=C4, 1 ,’’’’). Copy that down to all of our cells and then once the ones or blanks are there, it's very easy just to use the SUM function to go through and sum up all of those 1 values =SUM(D2:D11).
So if you don't care about presentation you're just trying to get the total score using the 1 is the way to go.
Otherwise COUNTIF, great little function that we have and there's also a SUMMIT function that will talk about some of the days.
Thanks to Kyle for sending in that great question.
If you have a question please feel free to drop us a line, otherwise we'll see you next time for another netcast from MrExcel.
