#INVALID DATA TYPE error in formula

Natalia Kataoka

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.

SoS | Dan Palenchar

Try

=IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Declined") > 0, "DECLINED", IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Submitted") > 0, "PENDING", IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Approved") > 0, "APPROVED", "PENDING")))

Here are the various outputs

SoS | Dan Palenchar

Try

=IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Declined") > 0, "DECLINED", IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Submitted") > 0, "PENDING", IF(COUNTIF([Debbie Approval]@row:[Lance Approval]@row, "Approved") > 0, "APPROVED", "PENDING")))

Here are the various outputs

Natalia Kataoka

Great, that worked. Thank you!

SoS | Dan Palenchar

@Natalia You're welcome!