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

Koyaan

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

Genevieve P.

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

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.

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

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

Genevieve P.

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

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.

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

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

Have a nice day