How a space, particularly at the end of a cell, can cause errors in functions like IF, SUMIF, COUNTIF, VLOOKUP.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Space Error in Sumif, vlookup and other functions
The If functions in Excel including SUMIF and Countif, are extremely useful in that they will perform a certain action depending on the results of a cell.
However there is a risk to the way Excel does this. So for example what we’ve got here is some Part names, and how many Units were purchased, and here is a bit of a summary where it will tell you that Part name AAA, 3 were purchased.
How it does that is
it goes to the section and it looks for all the AAA’s
and then it adds up how many units were purchased
What is important to realize is, that as far as Excel is concerned when it looks for the AAA it’s looking for exactly AAA, and therefore if there is any misspelling it could be a problem.
Of more concern however is how Excel handles spaces so for example here you can see that
this correctly adds up to the total units purchased
and I’ve built a little error check in there,
let’s say for whatever reason someone goes into this AAA and adds something that cannot visible be seen, for example just add a space,
the net result is as far as Excel is concerned, this AAA space, is no longer the same as this AAA
and therefore it does not add it in,
This can cause major problems in a spreadsheet. The way to avoid this is first and foremost
you must build in some error checks
and I highly recommend with any If function, Coutif’s and Sumif’s to basically put all the possibilities down and add them up
and make sure that it equals the raw data,
The other alternate is to enforce that no errors can happen at this stage, the name stage and this can be done using data validation.
It is extremely important that you understand this, because if for whatever reason you had not included these sections here and been able to check it, the only other way to pick up that Excel was not adding up the correct cells would be to manually do it. And the whole point of Excel is to avoid manual calculations.
So it is very, very important to build error checks into your spreadsheets