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

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓

    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


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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
    Mark Cronk ✭✭✭✭✭✭

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!