Why does the SUM function always deliver the value "0"?

Hello everyone,

I have integrated my smartsheet account with a formbuilder service (123 formbuilder). Smartsheet perfectly shows and updates all data which I gather through my various forms. Integrating all data into one giant mastersheet works fine as well. But I am experiencing a small problem with the SUM function.


Wheter I am

(1) calculating within the table itself

(2) using the sheet summary function or

(3) using "Σ Summarize" in the report toolbar (this is the one I would prefer to use)


it always delivers the value "0". All columns I need to summarize are text/number columns. Is it possible that the function does not recognize the values within the columns as numbers?


How can I solve this little issue?


Cheers to everyone

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Koyaan

    Yes, you can use a VALUE function to translate the numbers! You'd want to set up one "helper" column for each column with numbers you'll want to convert.

    Then the formula would be as simple as the following:

    =VALUE([Column Name]@row)

    You can make this a Column Formula (see here) to make sure that every new row has this formula applied. Then you can even hide your two original columns and use your helper column as the source for your formulas and reports. Does that make sense?

    Cheers!

    Genevieve

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @Koyaan 

    Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Koyaan
    Koyaan ✭✭

    Hello Bassam, thanks for the answer. Here is a screenshoor from the report. I am also counting the column "Ref ID" and that works perfectly fine.



  • Koyaan
    Koyaan ✭✭

    and this is the formula i used via the sheet summary function


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Koyaan

    It looks like the formula you have in the Umtausch column and the one in the Auszahlung column are changing your numerical values into text, you're correct!

    What are the formulas in those two columns?

    We can likely use a VALUE function to make sure the number is returned as a numerical value. Or, if you have "quotes" around your formula output this could be changing it to text, so we just need to remove those.

    Cheers!

    Genevieve

  • Koyaan
    Koyaan ✭✭

    Hello @Genevieve P , thanks for the answer.


    There were indeed "quotes" around some of the values. I have changed that manually and the SUM function recognized them now. But not all values are in "quotes".


    I do not use any formulas to get the values within the cells of each column. A new row is added to this sheet whenever someone uses my forms.


    Is there a solution to make sure that all values in these columns are always shown as numerical values? Could a VALUE function be used in a way that incoming data gets transferred to numerical values automatically?


    Changing the values manually is no option since I intend to use my mastersheet as a sheet that gets updated automatically. Automating such tasks is the reason why i think smartsheet is a great tool.


    Thank you and have a nice day

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Koyaan

    Yes, you can use a VALUE function to translate the numbers! You'd want to set up one "helper" column for each column with numbers you'll want to convert.

    Then the formula would be as simple as the following:

    =VALUE([Column Name]@row)

    You can make this a Column Formula (see here) to make sure that every new row has this formula applied. Then you can even hide your two original columns and use your helper column as the source for your formulas and reports. Does that make sense?

    Cheers!

    Genevieve

  • Koyaan
    Koyaan ✭✭

    Thank you very much @Genevieve P . I tried your approach and it works smoothly.


    Have a nice day

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!