Make Cell Blank if all Columns are "N"

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!

SmartSheet 2.PNG

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.