SUM calculation skipping a cell

Which version of Excel are you using?

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Please update your forum profile with your Excel version NOW.

It sounds as if there is something else in the cell.

Try =LEN(A1) - does it return the correct character count? If not, how many extra invisible characters are there?

Have you tried:

=TRIM(A1)

=CLEAN(A1)

Can you upload a sample file with some representative data (single column) that shows the issue when you get home?

If there is no alignment formatting on the cell, text values will be left aligned and numeric values (numbers, dates, times, etc.) will be right aligned. Visible leading zeroes on a numeric value can also be an indicator that the cell is formatted as text.

To highlight cells:

Select cell A1 > conditional formatting rule > formula rule > =ISTEXT(A1) > set required formatting > save > Applies to: $A:$A.

But it's better to resolve the problem globally if you can.

Then you may have a non-breaking space in the cell.

What happens if you do this?

=N(A1)

Does it then still report as being text in the formula cell, or a number?

Also try:

=SUBSTITUTE(A1,CHAR(160),"")

Try selecting the column containing the numbers.

Find & Replace > in the Find box, hold down the ALT key and type 0160 on the numeric keypad, then release the ALT key. Click Replace All.

Does this resolve the issue?

There's a space there, but which one!

Try find and replace with ALT+0255 instead.

EDIT: You MUST have the whole column selected when you do the F&R.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.