Complex SUMIF/SUMIFS Issue

Susan Portnoy

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

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

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