SUM cells with formulas not working

Eva M

Hello, I have a sheet with a column of cells with formulas in them. These formulas produce numbers. Then I have a second sheet, where I have a formula in a cell that references the first sheet's column with numbers. This formula in the second sheet is asking to SUM some of the numbers in the first sheet's column and divide it by their COUNT. So, for example, I am asking to reference Sheet 1, and sum 2+3+4+5+6 and divide that by 5. However, the totals are coming out wrong. I saw in another post that a person was saying that if one tries to SUM cells with a formula in them, the SUM will be wrong. Is this the case? I only have numbers, no text in the cells. Thank you!

Andrée Starå

Hi Eva,

Can you maybe share some screenshots and what formulas you're using? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

"I saw in another post that a person was saying that if one tries to SUM cells with a formula in them, the SUM will be wrong. Is this the case?"

 

This is not necessarily true. There are a handful of different variables that come into play to include the formula producing the numbers, the data the numbers are being pulled from, column types, etc.

Eva M

Hi Andree, Thanks so much for the quick response! I am attaching screenshots of my two sheets. I didn't have any formal training with Smartsheet so I've been self-teaching. It's not that easy considering what my boss wants from me. :-)

Now that I see the screenshots, is it too small to read or can you enlarge it? 

Eva M

SO I just figured out that it actually is working properly - I was just including the wrong days in my own calculations. I should have excluded June 17 and 18 from my addition because today is the 25th...so 7 days worth of numbers for the report would be 25th, 24th, 23rd, 22nd, 21st, 20th, and the 19th. Wheeew. OMG. So my formulas are actually correct and I don't have a question anymore. Now I feel dumb. I guess I needed to write it out somewhere to arrive to this conclusion. Thank you anyway for trying to help!

Andrée Starå

Happy to help!

Easy to miss!

Glad you got it working!

Lindsay AR

I am having issues with sum not working with formula fields. The answer just returns 0. 

 

 

Paul Newcome

Your numbers are actually being stored as text. Can you provide the formula that is returning the numbers you are trying to sum?

Lindsay AR

Brilliant! As soon as you said that I realized I had " " around my return values. Once I took those out, the formula worked.

Paul Newcome

Excellent. Glad you got it working. 

Kyle Walker

Having the same problem, I assume it is because my number might show as text but not sure how to fix that.

Kyle Walker

Sunil Kadikar

I'm having this same issues. I've built a summary table to count the number of "Issues" that meet certain criteria. I'm using a the COUNTIFS formula to do this.

This summary sheet feeds a dashboard that I use pie chart on to visually show the breakdown of Issue categories. Piecharts show an error in Smartsheet when they have a zero value to display, so I am using a workaround that someone else posted to have a category that has a count of 1, when everything else Sums to zero, I'm using a simple =(IF(SUMA1:A2)=0,1,0) formula for this.

The problem is that it seems to pull a SUM of 0, even with the COUNTIFS formulas are calculating a value that is greater than zero

Any advice?