Periodically, you may encounter numbers in Excel that you can’t sum or use arithmetically. A common cause for this is numbers formatted as text. Often, reports exported from other programs, such as an accounting package, will be formatted as text or they might contain embedded spaces. Why are my Excel sums not working?
The most common answer is: it seems that the cells you are trying to sum are formatted as Text. They may look like numbers but in reality they are being treated as Text not numbers. Copy a blank cells (without any cell content in it) –> Select the cells you were trying to Sum –> Right click on one of the selected cell -> Choose Paste Special –> Select Add and click OK.
Copy a blank cells (without any cell content in it) –> Select the cells you were trying to Sum –> Right click on one of the selected cell -> Choose Paste Special –> Select Add and click OK. Your Sum formula will return the correct output then. Treat people the way you want to be treated. Talk to people the way you want to be talked to.
What is the purpose of the sum () function in Excel?
Excel is telling you (in an obscure fashion) that the values in A1 and A2 are Text. The SUM () function ignores text values and returns zero. A direct addition formula converts each value from text to number before adding them up.
Why won’t the sum function work on a text?
But if you format a text as a NUMBER it will still be a text and the SUM function will not work. Say you have ” 10″ in A1. This a is a text. Try entering the formula =VALUE (A1) to make the text that looks like a number into a real number.
Is it impossible to sum numbers in Excel?
Re: Impossible to sum numbers in Excel 1 Go to Data Tab. 2 Click on Text to Columns and click on Finish in the next window which pops up which is Text to Columns wizard window.
How to sum numbers with text values in Excel?
Mike H.. Looking at you screencap we can see all the numbers are left justified which may suggest that despite the formatting they are text values. Try this:- Put a 1 in a spare cell and select and copy that number. Select your numbers and then right click | paste special | select multiply and click OK and try your sum formula.
How to fix numbers that don’t add up in Excel?
How to Fix Excel Numbers That Don’t Add Up To fix numbers that are seen as text, follow these steps: Right-click a blank cell, and click Copy Select the cells that contain the “text” numbers.