SUM cells with formulas not working
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!
Comments

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, [email protected])
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

"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.

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 selfteaching. 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?

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!

Happy to help!
Easy to miss!
Glad you got it working!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:[email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

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

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


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

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?
Help Article Resources
Categories
Check out the Formula Handbook template!