Home // Video Tutorials // 3 - 6 (Intermediate) // 5 // Excel error check using COUNT

Excel error check using COUNT

How to perform excel error check using COUNT function to identify errors in your spreadsheet.

Excel error check using COUNT

The various COUNT functions in excel are very useful for performing error checks. In this example we will go through some of these. In this example we have a number of cells with numbers in them & all we want to do is check whether these numbers are even or not, as shown in this formula. What we would like to build is a single check that will look through these checks & identify if any one of them is an error, so that we can see in one cell whether entire of cells have an error in them. First, let’s understand what the COUNT function does.

In this cell I click on the FUNCTION WIZARD, and I find the COUNT FUNCTION & I say OK. What you’ll see it tells you that the COUNT FUNCTION counts the number of cells that contain numbers & numbers within the list of arguments. So in the values of this example I’m going to highlight all of these & when I say OK, you’ll see the COUNT FUNCTION comes up with zero because there are no numbers here. Excel has another function called COUNTA & if we activate that function you’ll see what it says it counts the numbers of cells that are not empty & the values within the list of arguments. So, in this case I’m now going to highlight the same area & when I click OK, you’ll see it tells me that there are 12non blank cells here. In order to check now whether all of these are ok or whether there’s a problem cell. We can now do COUNTIF & then compare it to the number of non-blank cells. For example: The COUNTIF FUNCTION, if I activate it, I can now tell it to look in a certain range. You highlight that range & then it asks for the criteria & what I want it to do is to count how many ok’s there are in this area. I’ll put the word OK, & when I click ok, you can see that COUNTIF tells you there a re10 OK’s in this area. You can now use this information to check whether all these cells indicate OK. So in this cell, I’m going to use an IF FUNCTION. The logical test for the IF FUNCTION, we know that this cell indicated 12 – it included the header & boarder cells. So what we want to do is say that if this cell minus those two items is equal to the number of OK’s found. If that is true we can say ok. If it is false, you can say check. When I say ok, we’ll have our error check. Let’s say we go here & this number changes to an odd number, you’ll see it immediately shows the error. You can go back to the original & it now shows that all these cells are the same. In this example we’ve used the number of cells to get our answer, but it is possible to put all of this into one formula & a way to do this would be: Click on the final formula you’re looking at, just copy that part of it. Now we can see this D8 is coming from here & this has a formula in it so, for now we’ll get out of here, we go to D8, now copy the formula in D8, copy it, push escape button to get out of it, come back to the formula here & I replace the D8 with the formula we just coped. So now we’ve got IF COUNTA equals -2 equals D9, push enter to get out of the cell. D9 had another formula in it & again I can copy that formula, push escape to get out of it, come down to my single formula & where the D9 is, I can replace the reference with actual formula & what you see we have quite a comprehensive formula & when I push ok, you’ll see it acts exactly the same way as our 3 cells here. So if I go here, change that to 3, both formula’s change, so you can either do it this way, split it out over a number of cells to make it easy to understand or you can build it all into one cell.