#INVALID DATA TYPE error in formula

Hello. I have a formula which work fine for 2 out of 3 variables, but not the last and I cannot figure out what is wrong.

Use case: I want the Waiver Approval Status column to read two approval process columns and return Declined if either row shows 'declined', 'pending' if either row shows 'submitted' and 'approved' if both show approved. This is the formula:

=IF(COUNTIF([Debbie Approval]5:[Lance Approval]5, "Declined"), "DECLINED", IF(COUNTIF([Debbie Approval]5:[Lance Approval]5, "Submitted"), "PENDING", IF(COUNTIF([Debbie Approval]5:[Lance Approval]5, "Approved"), "APPROVED", "PENDING")))

The only portion that doesn't work is if both Debbie & Lance's approval columns show 'approved' then I get the error. Every other situation works. Debbie & Lance's Approval columns are single select drop downs, the Waiver Approval Status was originally the same type (single select dropdown), but I've also changed it to Text/Number with the same result.

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!