# SUM cells with formulas not working

✭✭
edited 12/09/19

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!

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | 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.

• ✭✭
edited 06/25/19

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?

• ✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | 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.

• ✭✭✭✭✭✭

Excellent. Glad you got it working.

• ✭✭✭✭

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