Complex SUMIF/SUMIFS Issue

Hi - I am new to Smartsheet and I am trying to put a formula in that I have in a spreadsheet, but it appears Smartsheet is very different and I keep getting errors and not sure what to do. Here is the formula I have in the spreadsheet and would appreciate any help in converting this to Smartsheet language!

Spreadsheet formula:

=SUM(IF(N2<>"IMPLEMENTED",I(N2<>"CLOSED",(NOW()-G2),(O2-G2))))

N2 = PROJECT STAGE

G2 = DATE PRIORITIZED

O2 = STAGE UPDATE DATE

I am trying to get the aging results from the date calculations in a fraction into a column 'AGE FROM PRIORITIZED DATE'. However, if the project is 'Implemented' or 'Closed', then instead of using the NOW portion of the formula, I am taking the 'STAGE UPDATE DATE (i.e. Date it was Implemented or Closed) against the Date Prioritized to determine how long it took to implement using fraction (i.e. 10 1/4 days).

Hopefully, this makes sense and someone can educate me on how to do this!

Thanks,

Susan

Find more posts tagged with

Accepted answers

Mark Cronk

Hi Susan,

Try:

=IF(ISDATE([date prioritized]@row), IF(OR([project stage]@row = "implemented", [project stage]@row = "closed"), [stage update date]@row - [date prioritized]@row, today() - [date prioritized]@row))

Work or error?

Mark

All comments

Mark Cronk

Hi Susan,

Try:

=IF(ISDATE([date prioritized]@row), IF(OR([project stage]@row = "implemented", [project stage]@row = "closed"), [stage update date]@row - [date prioritized]@row, today() - [date prioritized]@row))

Work or error?

Mark

Susan Portnoy

Mark!!! OMG - thank you so much for the help! I never would have gotten that, and now that you have shown me how this works I understand more how to put these together. I cannot say I won't reach out again, but this is great. Thank you!! Susan

Mark Cronk

Hi Susan, Happy to help. Glad you found a solution. Thank you for contributing to the Community.

Mark

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.