I have been pulling my hair out because the worksheets I have been using and putting a lot of work into stopped performing simple SUM calculations, i.e. SUM(A1:A5+B1:B5). I always got a #VALUE! error no matter what I did to correct it. I tried everything I could think of but the only thing that worked was to make the formula
SUM(A1:A5)+SUM(B1:B5)
Why the worksheet, which has been working normally for 6 months just suddenly stopped working is beyond me.
Anyone have an idea why this happened?
TIA
If there are any TEXT entries in either range then this syntax could return the #VALUE! error:
=SUM(A1:A5+B1:B5)
The syntax you say you are now using will ignore the text entries:
=SUM(A1:A5)+SUM(B1:B5)
However, you can shorten that to:
=SUM(A1:B5)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Your original formula was probably:-
Please Login or Register to view this content.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
So if i delete the cell contents and re-write the formula, why does Excel retain any text? Shouldn't deleting all contents delete all text references?
Thanks again
I think we'd need to see a sample file to figure out what you're trying to do.
Just now in this worksheet, I put the number 4 in three cells F122, G122 and H122, then in another cell I referenced those three cells by the formula =F122:H122. I get #VALUE!. It used to sum the numbers, but not any more.
What are you intending to do/what result do you expect with that formula?
All I want to do is add the three simple values in cells F122, G122 and H122 and put that sum into another cell. That other cell then stands alone as a sum. Why does the usual SUM(F122:H122) result in a #VALUE! rather than a numerical value?
=F122:H122 is not a valid sum formula.
=SUM(F122:H122) is a valid sum formula.
Are there #VALUE! errors already in any of the referenced cells?
If not then we'll need to see a file with the formula entered.
Thanks for your help. So it should be =SUM(F122:H122) which works. Can I also do this for multiple sums:
=SUM(F122:H122+F123:H123) or is this incorrect? I am old and confused after spending 6 months creating this spread sheet for work.
Don't use the plus sign +.
If the range of cells is contiguous then just do it like this:
=SUM(F122:H123)
If there are multiple non-contiguous ranges then separate them with commas like this:
=SUM(F122:H123,J1:J10,K25:L50)
If they are multiple single cells (and none of them contain TEXT entries) then it's best to use the plus sign like this:
=A1+B5+C10+D200
Nice tips which I have cut and pasted to my forehead.
Maybe I can get something accomplished now!
Best to you
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks