Error Message- Boolean expected

Jessica Howell

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!

Cory Page

=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

@Cory Page This doesn't return another expression

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

Cory Page

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

@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

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)