Space Error in Sumif, vlookup and other functions

How a space, particularly at the end of a cell, can cause errors in functions like IF, SUMIF, COUNTIF, VLOOKUP.

Related-View our course on cleaning data with Excel

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