SUM function not working on cells with formula

Hi,

I faced an issue with the SUM function, since it seems to not work on a column in which the cells contain a formula to return a number.

The cells that i want to SUM contain (JOIN(COLLECT)) formulas that pull data from another sheet. When I insert =SUM([Total Price]1:[Total Price]70) the SUM just returns 0. Also if I highlight the column by clicking on the header the SUM is not showing up in lower right corner as it does otherwise. The type of the column is Text/Number.

What am I missing for the SUM to work properly?

Thank you for any advice in advance.

Dora

Find more posts tagged with

Accepted answers

Paul Newcome

Or wrap your JOIN/COLLECT in a VALUE statement to convert it to a numerical value after being pulled by the original formula.

=VALUE(original formula)

All comments

Alejandra

Hi @Dora Berky,

I believe this is happening because the JOIN function combines a range of cells into a string (with optional delimiters between the values). In order to sum this range of cells, you must remove the JOIN function.

If needed, more information on JOIN can be found here: https://help.smartsheet.com/function/join

I hope this helps!

Paul Newcome

Or wrap your JOIN/COLLECT in a VALUE statement to convert it to a numerical value after being pulled by the original formula.

=VALUE(original formula)

Dora Berky

Thank you for the advises! Adding the VALUE statement solved the issue, I will use this from now on :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.