Convert a Sum into Currency format $#,##.00

I have a formula that creates a note and within that note is a sum of numbers. For example, JOIN("This is your total: $" + round(sum([Number1]@row, [Number2]@row), 2))

When I use this formula, the results look like "This is your total: $34000.00" when I need it to return "This is your total: $34,000.00".

When I use FunctionAssist it tells me to use the TEXT function but this doesn't exist in SmartSheet??

Any ideas?

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Here is the base formula for adding commas up to 999,999,999,999.99

=IF(LEN(INT(ABS([Total Column]@row))) <= 3, RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 6, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 9, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 8, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3), IF(LEN(INT(ABS([Total Column]@row))) <= 12, IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 11, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 8, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 5, 3), LEFT(INT(ABS([Total Column]@row)), LEN(INT(ABS([Total Column]@row))) - 3)), "") + "," + RIGHT(INT(ABS([Total Column]@row)), 3))))) + IF([Total Column]@row - INT([Total Column]@row) = 0, ".00", RIGHT([Total Column]@row - INT([Total Column]@row), 3))

All comments

ECarlson_PCG

="This is your total: $" + ROUND(SUM([Number1]@row, [Number2]@row), 2)

Hope this works for you.

srigs

I'm still missing the comma which separates the number place - i.e. it returns $3000000.00 but I need the format $3,000,000.00.

Thanks!

Paul Newcome

Here is the base formula for adding commas up to 999,999,999,999.99

ECarlson_PCG

@srigs

Paul figured this out on this thread:
Insert Commas into Formula for Dollar Amounts

I duplicated his formula successfully.

I'd recommend making 2 columns, one for calculating the total (and an easy name for all the references, I chose "A"), and the second for his + your text formula for your desired end result.

column A:

=ROUND(SUM([Number1]@row, [Number2]@row), 2)

Custom Formula using Paul's script:

="This is your total: $" + IF(LEN(INT(ABS(A@row))) <= 3, RIGHT(INT(ABS(A@row)), 3), IF(LEN(INT(ABS(A@row))) <= 6, IFERROR(IFERROR(MID(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 5, 3), LEFT(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 3)), "") + "," + RIGHT(INT(ABS(A@row)), 3), IF(LEN(INT(ABS(A@row))) <= 9, IFERROR(IFERROR(MID(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 8, 3), LEFT(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 5, 3), LEFT(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 3)), "") + "," + RIGHT(INT(ABS(A@row)), 3), IF(LEN(INT(ABS(A@row))) <= 12, IFERROR(IFERROR(MID(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 11, 3), LEFT(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 9)), "") + "," + IFERROR(IFERROR(MID(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 8, 3), LEFT(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 6)), "") + "," + IFERROR(IFERROR(MID(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 5, 3), LEFT(INT(ABS(A@row)), LEN(INT(ABS(A@row))) - 3)), "") + "," + RIGHT(INT(ABS(A@row)), 3))))) + IF(A@row - INT(A@row) = 0, ".00", RIGHT(A@row - INT(A@row), 3))

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.