# I need to skip blank columns in rows

✭✭✭✭

This is my current formula:

=IF(AND([BO1 approval]@row = "Approved", [BO2 approval]@row = "Approved", [BO3 approval]@row = "Approved"), "Approved", IF(OR([BO1 approval]@row = "Denied", [BO2 approval]@row = "Denied", [BO3 approval]@row = "Denied"), "Denied", "Submitted"))

How it works:

If all approvers hits "Approved" =Approved, if any one of them hits"Denied" then it's denied.

Issue: In some cases, all of the approval columns won't be filled. e.g.

BO1 (Bob) - Approved

BO2 (blank) - blank

BO3 (Sara) - Approved

However, I'd like the same logic to apply.

• ✭✭✭✭✭✭

Try this:

=IF(COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Denied") > 0, "Denied", IF((COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Approved") / COUNT([BO1 approval]@row:[BO3 Approval]@row)) = 1, "Approved", "Submitted"))

Let me know if it works!

Best,

Heather

• ✭✭✭✭✭✭

@Tsega Thompson Oh, yes! Here you go:

=IFERROR(IF(COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Denied") > 0, "Denied", IF((COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Approved") / COUNT([BO1 approval]@row:[BO3 Approval]@row)) = 1, "Approved", "Submitted")),"")

«1

• ✭✭✭✭✭✭

Are all of your approval columns next to each other or are there columns in between that could also contain "Denied" or "Approved"?

• ✭✭✭✭✭✭

Try this:

=IF(COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Denied") > 0, "Denied", IF((COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Approved") / COUNT([BO1 approval]@row:[BO3 Approval]@row)) = 1, "Approved", "Submitted"))

Let me know if it works!

Best,

Heather

• ✭✭✭✭

Hi Paul,

They are all next to each other.

• ✭✭✭✭

Thanks Heather that seemed to do it!

• ✭✭✭✭

Unfortunately, that fixed something and broke something else. If Bo1-3 are blank I get #DIVIDE BY ZERO in that column.

However, that column feeds a function in another column. Is it possible to make it blank instead?

• ✭✭✭✭✭✭

@Tsega Thompson Oh, yes! Here you go:

=IFERROR(IF(COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Denied") > 0, "Denied", IF((COUNTIF([BO1 approval]@row:[BO3 Approval]@row, "Approved") / COUNT([BO1 approval]@row:[BO3 Approval]@row)) = 1, "Approved", "Submitted")),"")

• ✭✭✭✭✭✭

@Heather D That's pretty close to the route I was going to take, but I wanted to make sure that there were no columns in between that could skew the results.

• ✭✭✭✭✭✭

@Paul Newcome I figured, but I apparently threw caution to the wind today 😁

• ✭✭✭✭

that worked. Thanks again!

• ✭✭✭✭✭✭

@Heather D Hahaha. Living on the edge.

I personally read it this way (@Tsega Thompson please correct me if I am wrong)... There are three possibilities for a cell. "Denied", "Approved", and blank. There is the possibility of an "Approved" and two blanks, but in that instance we want it to say "Approved", but "Denied" takes priority.

I would have gone with a CONTAINS instead of the COUNTIFS (but it is the same idea of looking across the range for at least one instance).

=IF(CONTAINS("Denied", [BO1 approval]@row:[BO3 Approval]@row), "Denied", IF(CONTAINS("Approved", [BO1 approval]@row:[BO3 Approval]@row), "Approved"))

Of course if there is in fact a "Submitted" status, then we would need to throw that into the mix...

=IF(CONTAINS("Denied", [BO1 approval]@row:[BO3 Approval]@row), "Denied", IF(CONTAINS("Submitted", [BO1 approval]@row:[BO3 Approval]@row), "Submitted", "Approved"))

• ✭✭✭✭✭✭

@Paul Newcome Ooh! I like that one better. Anything to make a formula more concise!!

• ✭✭✭✭✭✭

@Heather D It is basically the same concept but with a different approach. Searching the range for a key word.

• ✭✭✭✭

Just curious how would it have worked if the columns weren't in a range?

• ✭✭✭✭✭✭

You would need to use OR statements in place of the contains and specify each cell.

=IF(OR([B01 Approval]@row = "Denied", [B02 Approval]@row = "Denied", [B03 Approval]@row = "Denied"), "Denied", IF(OR(..............................), "Submitted", "Approved"))

It isn't too bad with only two or three columns, but much more than that can become rather tedious.

• ✭✭✭✭

noted with thanks.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!