Sheet Summary Report Formula - #Unparsable Error

Options

Hi - I created a sheet summary report for 4 project. My Total At Risk column is coming back with the error #UNPARSEABLE even though I used the same formula for that column. Can someone give me some guidance on what I could do to troubleshoot the issue? Here is the Total At Risk column formula that I used for each sheet: =IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Complete")), 1, 0)

Answers

  • Denden8706
    Options

    Try

    =IF(AND([End Date]@row < TODAY(), NOT([Status]@row = "Complete")), 1, 0)

    or

    =IF(AND([End Date]@row < TODAY(), [Status]@row <> "Complete")), 1, 0)

  • Craig Cuva
    Options

    Thanks for the attempt. I should clarify that my formula works and is applied to Sheet Name "Cocoa and Bean Project" which gives me a number. I used the same formula for the other 3 sheets and I get an unparseable error.

  • dojones
    dojones ✭✭✭✭
    Options

    I agree that your formula works. Check to make sure the field types and names are the same on the other sheets.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Craig Cuva

    Can you post the formula you're using for the "Cocoa and Bean Project" sheet?

    Since you're using the Sheet Summary field, you won't be able to reference @row in your formula, as that looks "at the current row"… but summary fields do not exist in a row.

    Is it possible that you have an [End Date] summary field that you want to reference in your formula instead? If so, you would use the # symbol to link to that cell, and the Status cell:

    =IF(AND([End Date]# < TODAY(), NOT(Status# = "Complete")), 1, 0)

    Cheers,
    Genevieve

  • Craig Cuva
    Options

    Hi @Genevieve P. ,

    The formula for Cocoa and Bean is the same as the other project sheets. The exact same formula works while the others do not. I am using a sheet summary report, not a sheet summary field.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Craig Cuva

    I think we may be using the same term for different features - can you post a screen capture of your formula open in the sheet?

    Reports are unable to reference cells in formulas, so the formula itself would be built in the underlying sheet (and then surfaced in the report). It would be helpful to see a screen capture of that sheet! 🙂 But please block out sensitive data.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!