Cell values not detected

[hermithead]

I have an issue where my SUMIF formula is not detecting the values in a cell and thus returning a zero result. The formula is referencing values from a spreadsheet that I have exported directly from an IS. I have checked and modified the cells formatting ie General, Number, Text but this hasn't made any difference. The only way I have got the formula to work is by manually re-typing the values into the cells being referenced. Does anyone have an idea as to why these values are not being detected?

[DonkeyOte]

Altering the format of a cell does not affect the underlying data type, for example:

A1: Apple

Altering format of A1 to Number to 2 decimal places doesn't convert Apple to a number... the same is true of any text string.
You need then to "coerce" the values from numbers stored as text to numbers - this you've found you can do by re-entering the value, XL sees this as a number and acts accordingly... however if you have lots this isn't viable.
So - how to coerce en masse ?

If your values are in a single column: highlight the column and run Data -> Text to Columns -> Finish

If spread across multiple columns: enter 1 into a blank cell, copy it, highlight all values to be coerced -> Edit -> Paste Special -> Operation: Multiply