WARNING!! DO NOT TRUST THE SUM FUNCTION - adding up a column of numbers using SUM does not always wo

We have recently found that the SUM function does not always work - usually this is fine as it shows an error. However, we have just found an example where there was no error, but the SUM was not correct. It was just ignoring one of the fields

DO NOT TRUST IT

Find more posts tagged with

Comments

Travis

Chozza, I have never heard of the SUM function not working. Could you please post a screenshot of an example of how the SUM is not working?

If your data is sensitive and you would prefer not to post it, would you share the sheet with me and point out where it is not working?

travis.hannon@smartsheet.com

Thanks!

Chozza

Hi Travis

Really i would like to fix your problem, but it took us so long to work out where the error was - many hours, literally - the though of havnig to go back and help you problem solve is not very atttractive to me.

Although we have now given up on Smartsheet i do like what you guys are doing i do want to help so here is what we found:

all numbers in the sheet looked fine and formatted corrently.

Sum function was not showing any errors and giving an answer

however, one cell for some reason was being ignored, even though when you checked the formula it was clearly included.

when we deleted the cell and re-added the number it worked fine again.

However, there is no way you can expect people to use a spreadsheet system which is as flaky as this -

hope this helps

andy

Travis

Thanks for the reply and I am sorry you had to spent so much time troubleshooting. If you see this issue again, please let us know.

I have worked with hundreds of sum formulas over the years and have never seen (or heard of) any issues with accuracy. It is possible there was a formatting issue that might not have been apparent but it's hard to say without seeing the sheet.

In the future, please make a copy of the sheet, take screenshots, or provide any sort of evidence to help us troubleshoot and determine the cause.

The Support team (support@smartsheet.com) will typically reply within one business day and can potentially save you time you would have spent troubleshooting. There are some cases where the issue may not be apparent to the user, but the Support team can spot right away.

Brad Jones

I have one for you Travis.

The SUM function does not always work if you are using it to SUM cells that have formulas in them. And it is strange, because it does not follow a pattern that I can tell. I tried to sum six cells with the formulas in them, and it worked fine, but when I threw on a seventh cell with a formula the calculation went bonkers.

In the attached sheet you can see that the sum of the cells is 65144, when the real total is 74

Is there some trick to getting the sums to work if the cell contains a formula? Is there a limit to how many cells it can handle that contain formulas?

J. Craig Williams

Brad,

It looks like the blank cells are causing Smartsheet to convert the values to text.

Rows 1359 through 1365 = 65

+ "" +

Rows 1373+ are now treated as text so

65+"1"+"4"+"4"

Try changing the individual rows to either VALUE or blank=0

Craig

Brad Jones

Thanks Craig for the feedback. It seems very strange to me for a program to add numerical values like 4 with text values like "".

What you suggested is possible, and i could use it if it did not contribute to visual clutter of the chart. Each of the 'blank' cells in the picture have a formula in them creating a sum of children in a section. If there are no children, then nothing is displayed. If there are child sums then they will show up in those cells. I suppose that it would be possible to change each row to output a "0" each time there is no child sum, but then I end up with a chart littered with extra zeros. I really want to avoid the unnecessary visual noise telling me that there is nothing there.

So in effect, the statement from the OP is true. You cannot blindly trust that the SUM function in SS works correctly - (unless you are one of the few who do ascii value math).

I would call this a bug.

J. Craig Williams

I would call it a bug too.

I think Excel also has a problem with blanks sometimes, but I don't have a platform to test. Oh I remember, it would treat a 0 as a value but blank as no value, so averages would sometimes seem strange.

I like some other programming languages approach -- if it looks like a number, I'll use it as number unless you (the developer or user) have told me not to. If it doesn't look like a number, then it must be text.

There's also some problems that Smartsheet uses "+" for both addition and concatenation. That was, in my opinion, a mistake. It was (likely) a design decision because non-techies likely get confused by & or some other symbol for concatenate.

I can see why it was done, but I don't have to like it.

Your series of what you think are sums are both additons and concatenations.

Phone numbers and zip codes, especially those that start with 0 are a pain.

Try changing your sum to =VALUE(cell1) + VALUE(cell2) + ...

The visual won't change but the summation might.

Craig

Travis

Brad, in formulas, you can designate text by using quotes: ""

=IF(Checkbox1 = 1, "Done")

This will show Done if the checkbox is 1.

This also allows us to add text to the end of formula results.

=COUNTIF(Checkbox:Checkbox, 1) + " - Complete"

This will show: 10 - Complete

Or concatenate text from multiple cells:

=Cell1 + " - " + Cell2

Robert - Available

If your formulas have an IF condition to show "" if (for example) the statement is false, then this will be formatted as a text value.

As Craig mentioned, this is likely what is happening in your formula. You are adding numberical values, then concatenating text.

Rather than building a formula like this:

=Cell1 + Cell2 + Cell3 + Cell4 ...etc

Try using the SUM function, like this:

=SUM(Cell1, Cell2, Cell3, Cell4) ...etc

This will only sum the numerical values and will not concatenate if the cells are formatted as text.

Brad Jones

YAY!!!!

Thanks for that Travis! That was the trick I needed. Now it is displaying correctly

The key difference was using the SUM function vs simply performing a manual sum. I can see now that I was confusing what the OP had listed with the behavior I had seen because we were using different ways of summing. Thank goodness for the community, and our trusty Travis.

Jwilson19

I am having the same issue and I just joined. When I summed a total, the total was off by approximately 1 million dollars. How am I suppose to use the sum total?

Jose Munoz

Im having similar issues when I sort the sheet the SUM results changes all the time