Trouble with SUMIFS - Project

Our sheets have built in deliverable risk management which we are working to roll up across projects. Right now we calculate a delay risk by looking at a risk/time allocation, and critical path - both which are columns within the same sheet. The following formula is working correctly:

=SUMIFS([Risk/Time Allocation (Days)]:[Risk/Time Allocation (Days)], [Critical Path]:[Critical Path], true)

I am trying to add an additional term to exclude risks where the associated deliverable has been completed, therefore looking at the "% Complete" column. The column is formatted as a %, returning 1 @ 100% complete. I want to return all values, including BLANK, which are not equal to 1.

The below is my best guess (but it isn't working):

=SUMIFS([Risk/Time Allocation (Days)]:[Risk/Time Allocation (Days)], [Critical Path]:[Critical Path], true, [% Complete]:[% Complete], OR(@cell <> 1, @cell = "")))

It appears that the <>1 piece of the function is returning "#UNPARSABLE"

Does anyone have any suggestions on an alternate term to try?

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!