Make Cell Blank if all Columns are "N"

tomsawchuk

Hi there,

Wondering how to amend this formula so that if all the cells are "N", it will return a blank in column "Report Status"

=IF(OR([PAR Items]16 = "Y", Proxies16 = "Y", Commissions16 = "Y", Constraints16 = "Y", [APX Reports]16 = "Y", [MRFP/SMA]16 = "Y", Commentary16 = "Y", Attachment16 = "Y", (COUNTIF([PAR Items]16:Attachment16, "") > 0)), "Incomplete", "Complete")

Thanks!

Nic Larsen

Try this. I combined with your other formula so it will check if all = N first and if not, it'll run through what you previously wrote. I also updated for @row to speed it up.

=IF(AND([PAR Items]@row= "N", Proxies@row = "N", Commissions@row = "N", Constraints@row = "N", [APX Reports]@row= "N", [MRFP/SMA]@row= "N", Commentary@row = "N", Attachment@row = "N"), " ", IF(OR([PAR Items]@row= "Y", Proxies@row = "Y", Commissions@row = "Y", Constraints@row = "Y", [APX Reports]@row= "Y", [MRFP/SMA]@row= "Y", Commentary@row = "Y", Attachment@row = "Y", (COUNTIF([PAR Items]@row:Attachment@row, "") > 0)), "Incomplete", "Complete")

Paul Newcome

What are the possible entries? The way I am reading your formula, if at least one cell is blank or one cell contains a "Y", it will show as Incomplete. How would you generate a Complete other than having all cells filled with an "N"? Having all cells filled with an "N" would currently generate a Complete result, so all you would have to do to generate a blank would be to change "Complete" to "".

tomsawchuk

Hi Paul,

I need it so that if any of the cells are "Y", it shows as incomplete and if any of them have initials (eg. TS), it shows complete. So right now, anything other than Y is causing the cell to say complete but I need it so that if it is all N's in the cells, it will be blank. 

Thanks,

Tom

 

tomsawchuk

I think this works! Thank you

Paul Newcome

Ok. That makes sense. The first thing to establish is how many possible entries there are. If there is the need for flexibility to allow for a differing number of columns whether you plan to add or remove, we can use a simple COUNTIFS across the range.

 

=COUNTIFS([PAR Items]@row:Attachment@row, OR(ISBLANK(@cell), NOT(ISBLANK(@cell))))

 

As long as [PAR Items] is the leftmost column and Attachments is the rightmost column, you can add, remove, or rearrange everything in the middle.

.

For the sake of this example though, I will use a hard coded number. Your screenshot shows 8 columns within the range, so I will use the number 8. If you need the flexibility of the COUNTIFS, just replace the number 8 with that formula within the overall formula.

.

So we will start with "Y". If any cell within the range contains a "Y", we want it to display "Incomplete".

 

=IF(CONTAINS("Y", [PAR Items]@row:Attachment@row), "Incomplete", 

.

Next we will say that if they are ALL "N", display a blank.

 

=IF(CONTAINS("Y", [PAR Items]@row:Attachment@row), "Incomplete", IF(COUNTIFS([PAR Items]@row:Attachment@row, "N") = 8, "", 

.

If there are zero cells with a "Y" in them making the first argument false, and not all cells contain an "N" making the second IF statement false, then the only other option would be to register it as "Complete" and close out the formula.

 

=IF(CONTAINS("Y", [PAR Items]@row:Attachment@row), "Incomplete", IF(COUNTIFS([PAR Items]@row:Attachment@row, "N") = 8, "", "Complete"))

.

And there you have it. Short, sweet, and to the point.