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

Copper Contributor

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.

 

Thank you for your time. 

7 Replies

Hi @BrandonS1973 

 

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

Sample.png

 

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?

@Lorenzo 

 

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.

 

BrandonS1973_0-1703935307559.png

 

@BrandonS1973 

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

image.png

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.

@BrandonS1973 

 

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.:

Sample.png

see attached sample

@SergeiBaklan 

 

Thank you for looking at my issue. Please see below

 

BrandonS1973_0-1703938223120.png

 

 

@Lorenzo 

 

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. 

@BrandonS1973 

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

image.png

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.