#value! error

[Barny72]

I have a workbook with 8 columns. Problem is that when applying a simple SUM formula I get the #value! error message. Some of the cells I am trying to Sum contain formulas so I think the SUM formula doesn't like this. I have tried various other formulas containing ISERROR/ISblank etc but nothing seems to work. I have also tried clearing spaces through the "find" function.

Any thoughts as to why this might be happening?

 

[Joe4]

Welcome to the Board!

The SUM formula simply ignores any text entries, so that would not be a problem.
However, it does have a problem is any of the cells are returning errors.
I am guessing some of your cells are returning errors.
If so, please provide the formulas returning errors, and we can help you fix it those.

 

[Kaper]

If you want just to ignore the errors (because errors in data are the most probable cause of final error) you may use instead of simple sum such a formula

=SUM(IF(ISNUMBER(A1:A100),A1:A100,0))

And as you are using Excel 2016 you shall commit this formula as an array formula. So after entering it use Ctrl+Shift+Enter instead of simple Enter.

Of course adjust the range A1:A100 to your needs (twice in the formula)

 

[Kaper]

You may also search for errors that way:
select the range to be summarized (like A1:A100 in example above)
press Ctrl+G
press special
select formulas and check only errors checkbox (uncheck all other checkboxes)
press OK.

cell(s) with error-generating formula shall be selected or ... you will receive message that there was no cells with errors

 

[Barny72]

Thanks for this. I tried the isnumber way but it still returns #value! error.

So just to explain further:

A1: contains the formula to sum cells b1+b2+b3
B2: contains another formula =IF(BT1<0,"1","") hopefully saying that if the result in BT1 is less than 0 then return 1 in b1
B3: contains the formula =SUM(b4:b10) e.g. totalling values in these cells.

There are no error messages appearing any other cell/columns.
I have noticed though that if i remove the formula from b1 and just manually input a figure/number, there is no #value error in A1.

Please note that this represents an extract from a workbook that contains other formulas and figures.

So basically I can't get the formula to work in A1 when the formula (as stated) is present in b1?

appolgies if this sounds confusing - do you think the workbook might be corrupted?

 

[Joe4]

A1: contains the formula to sum cells b1+b2+b3

That explains it! You are not using the SUM function, but instead manually adding up cells.
Note that the SUM function will ignore text entries, but using the addition symbol (+) will not. It will result in errors.
This is a subtle, but very important difference when using SUM versus +.

So use this formula instead, and it will get rid of the errors:

=SUM(B1:B3)

B2: contains another formula =IF(BT1<0,"1","") hopefully saying that if the result in BT1 is less than 0 then return 1 in b1

Note that this will return the text value of 1, and not the number 1. So the 1 will be ignored in your sum.
Everything enclosed in double-quotes is treated as literal text, and not as a number.
If you want the 1 included in your sums, remove the double-quotes from around the 1, i.e.

=IF(BT1<0,1,"")

 

[Barny72]

Hello - many thanks to all the replies. I have incorporated the advice given and all seems to be working fine now.

Many thanks guys