Is it possible to create a conditional rollup for $'s OR display the max value for %'s?

I am working on a smartsheet tracker that can track the cost savings of specific projects on a month to month basis, then rolls up the total YTD savings. There is also an option for the users to enter their project progress as a percentage, in which case I would want the YTD column to display the maximum % from the year (assuming that they entered cumulatively). I have tried a few different formulas to attempt this, but no luck. The formula will need to be a column formula, since I will not know whether the project will be entered into the template as $ or %.

The month to month tracking is done across a single row (January to December), if $ are present, I would like the cells to be totalled SUM([JAN-21:DEC-21]). If % are present, I would like MAX(JAN-21:DEC-21) to be displayed. My question is what would be the best method to mix the two into one formula?

=IF([JAN-21]32:[DEC-21]32 CONTAINS("$", [JAN-21]32:[DEC-21]32), SUM([JAN-21]32:[DEC-21]32), MAX([JAN-21]32:[DEC-21]32) ....

2021-06-01 15_43_25-Trenton Bowler CI Plan - Smartsheet.com.png

resulted in UNPARSEABLE

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Paul Newcome

The issue is going to be with the first portion of the IF statement. Try this instead...

=IF(CONTAINS("$", [JAN-21]32:[DEC-21]32), SUM([JAN-21]32:[DEC-21]32), MAX([JAN-21]32:[DEC-21]32)

The CONTAINS function outputs a true/false value, so it can be used as a "standalone" for a "logical statement".

Joseph Drees

Thank you, Paul!

Is there any way for my YTD column to format to % or $ (currency) based on the monthly data?

Paul Newcome

The formatting would have to be done manually. If you are only using the output for display, you could include the formatting in your formula, but that would then output text strings which you won't be able to add/subtract/etc.

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.