Forum Discussion
Problem with a SUM function not working properly
Something bizarre has happened with my Excel files. As an example, I have a cell that performs the operation =SUM(F4 + F5). The data put into that cell is correct for the defined operation. For purpose of discussion I will say that the piece of data is 450. I then go to the next cell immediately below that cell which should perform the next operation =SUM(F5 + F6). The entry that ends up in that cell is exactly the same piece of data as appeared in the cell with the operation =SUM(F4 + F5). In other words, 450 appears in that second cell. The correct piece of data should be 600. When I select the second cell where 600 should appear, the function for that cell is shown as =SUM(F5 + F6). So, the function is correct, but the entry is not. I have been working with this file of data for several years and never had this problem before. The problem exists for all of my calculated columns of data.
Coincidentally, the problem started after my computer, a Dell running Windows 10, experienced a series of updates. I do not know if this is all related, or not. Regardless, I need to get the file back to working the way it worked several days ago when it was doing what it was supposed to do. Any help would be greatly appreciated.
Hi Kenneth,
Are your Calculation Options set on Automatic?
6 Replies
- Look at the row line-numbers. if there are gaps, that could be your problem. For example: if rows 15 and 16 are missing and you're trying to sum rows 1 through 20 in column a: change =sum(a1:a20) to =sum(a1:a14)+sum(a17:a20) - until you figure out how to get all your row numbers back (you can try copying and pasting all your information into a new worksheet).
Look at the row line-numbers. if there are gaps, that could be your problem. For example: if rows 15 and 16 are missing and you're trying to sum rows 1 through 20 in column a: change =sum(a1:a20) to =sum(a1:a14)+sum(a17:a20) - until you figure out how to get all your row numbers back (you can try copying and pasting all your information into a new worksheet).
Hi Kenneth,
Are your Calculation Options set on Automatic?
- They are now! Thank you. Everything works properly again. For some reason the latest updates that the computer went through changed all sorts of setting in several programs including Excel. I never thought to check that setting. It had reverted back to manual and I was not aware of it.
Just as an FYI - you don't need the SUM() function here. SUM() is to add a range of data. You just need =F4+F5, or =SUM(F4:F5).
The SUM function above will expand to F4:F6 if you were to insert a row. But if you are just adding specific cells, just write =F4+F5+F9+J10 or whatever you need.
