I'm a New User having issues with a simple Sum

Copper Contributor

I'm a New User having issues with a simple Sum

Hi All

Im trying to do a simple pricelist in Excel.

Item A + Item B = AB, i now get #Value!

I have done a few of these before on my Work PC, I have a new one to do work at home, now I'm getting an issue. My Cells look correct. I have gone thru all the settings, auto calculate is on, I don't know what else to look for. I'm sure it's a setting as i have not had an issue before. I'm fairly new to Excel and still learning.

7 Replies

Re: I'm a New User having issues with a simple Sum

You usually get a #VALUE! error when you try to perform a calculation on non-numbers, like below:

Difficult to say more at this time. Could you post a picture showing what you have in your [Item A] & [Item B] columns and the formula you use to SUM these items?

Re: I'm a New User having issues with a simple Sum

Thank you for your assistance, please see below. I'm Taking Paper Cost (R0.14) Plus B&W Printing (R0.05) it should give me Cost (R0.19). The "R" Is for the Rand, The currency in my Country. I have done this before, not sure why it won't work this time.

Re: I'm a New User having issues with a simple Sum

Since values are left aligned it looks like you entered them as texts, not applied currency format in Rands to numbers

Which formula you use it's not clear from the screenshot. Please stay on the cell which returns an error and share screenshot which shows the formula in formula bar.

Even better if you could share sample file.

Re: I'm a New User having issues with a simple Sum

So you're doing exactly what I posted earlier: R0.14 isn't a Number, same goes with R.05. So logically when you add (multiply, substract...) these 2 Text values => #VALUE!

There's something to do IF the numbers you want to sum are always after R0.:

see attached sample

Re: I'm a New User having issues with a simple Sum

Thank you for looking at my issue. Please see below

Re: I'm a New User having issues with a simple Sum

Thank you again, I'm not sure why it doesn't work this time, as my previous ones I done were similar, with the "R" I hear what you are saying that Excl is seeing the "R" as a word, not part of the Sum.

Could you advise me going forward.

Re: I'm a New User having issues with a simple Sum

You have texts like R0:14, R0.14, etc, but they are texts. Formula like =TextA+TextB alvays returns #VALUE! error, you need to have numbers.

Try to enter into the cell the number like 0.14, Ctrl+1, select currency and apply proper currency format from the list

With that formula shall work. You still have numbers but shown in your local currency format.

In addition, yes, =SUM(B5:C5) ignores all texts and in your case result shall be zero (no one number in the range). However, =B5+C5 doesn't ignore texts and returns an error if any text is in the formula cells.