I need to skip blank columns in rows

Tsega Thompson

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.

Heather Duff

Hi @Tsega Thompson ,

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

Heather Duff

@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")),"")

Paul Newcome

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

Heather Duff

Hi @Tsega Thompson ,

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

Hi Paul,

They are all next to each other.

Tsega Thompson

Thanks Heather that seemed to do it!

Tsega Thompson

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?

Heather Duff

@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")),"")

Paul Newcome

@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.

Heather Duff

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

Tsega Thompson

that worked. Thanks again!

Paul Newcome

@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"))

Heather Duff

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

Paul Newcome

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

Tsega Thompson

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

Paul Newcome

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.

Tsega Thompson

noted with thanks.