Error Message- Boolean expected

Options

Good afternoon everyone,

I am using a checkbox column to count if an inspection has been completed for each quarter. I understand that anything greater than 1 in this type of column will result in "#BOOLEAN EXPECTED." What I'm wondering is...is there a way to change the error message into a "Duplicate Inspection"... or something like that? Formula for the checkbox column below,

=COUNTIFS({Level 1 Spill Kit - Main - Operations Range 1}, $Facility$1, {Level 1 Spill Kit - Main - Operations Range 2}, $Year$1, {Level 1 Spill Kit - Main - Operations Range 3}, Quarter@row)

Thanks!

Answers

  • Cory Page
    Cory Page ✭✭✭✭✭
    edited 08/08/23
    Options

    =iferror(COUNTIFS({Level 1 Spill Kit - Main - Operations Range 1}, $Facility$1, {Level 1 Spill Kit - Main - Operations Range 2}, $Year$1, {Level 1 Spill Kit - Main - Operations Range 3}, Quarter@row),"Duplicate Inspection") should do the trick

  • Jessica Howell
    Options

    @Cory Page This doesn't return another expression

    I'd it to return "Duplicate Inspection"… in the case its #BOOLEAN EXPECTED

  • Cory Page
    Cory Page ✭✭✭✭✭
    edited 08/08/23
    Options

    Hum, would you mind sharing a sample screenshot, also are you trying to check the box using the countifs formula?


    Update:

    I suspect you might want to wrap it in an if statement, just use your countifs statement instead of my count statement.

    =IF(COUNT([First Name]:[First Name]) > 1, "Duplicate Inspection", 1)

  • Jessica Howell
    Options

    @Cory Page Yes, I'm using this formula in a checkbox column. Its working fine, there's only an issue if the formula counts anything greater than 1

    =COUNTIFS({Level 1 Spill Kit - Main - Operations Range 1}, $Facility$1, {Level 1 Spill Kit - Main - Operations Range 2}, $Year$1, {Level 1 Spill Kit - Main - Operations Range 3}, Quarter@row)

    Example below. You can see it fills the checkbox in the General Ops column because there was only 1 inspection completed, but in the Level 1 column its giving an error message because the value is greater than 1 (in hindsight there should only be 1 inspection per quarter)


  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    edited 08/11/23
    Options

    Is something like this what you are looking for?

    =IF(COUNTIFS({Level 1 Spill Kit - Main - Operations Range 1}, $Facility$1, {Level 1 Spill Kit - Main - Operations Range 2}, $Year$1, {Level 1 Spill Kit - Main - Operations Range 3}, Quarter@row) > 0, 1, 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!