# Trying to ignore blank rows in a linked sheet

Options

Hello,

Paul helped me previously to create the below formula which is working perfectly. I am however having one issue, when all the cells are blanked, it is revealing "sometimes". I dont need it to count the blank cell. If all the cells are blank, I want the result to be blank. I tried using isblank, but no result. Any help would be grateful. Not sure if this is because the formula is created in a linked sheet.

Here is the formula

=IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, @cell = "N/A") = 4, "N/A", IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, OR(@cell = "No", @cell = "N/A")) = 4, "No", IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, OR(@cell = "Yes", @cell = "N/A")) = 4, "Yes", "Sometimes")))

• ✭✭✭✭✭✭
Options

Try this

=IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, ISBLANK(@cell)) = 4, "", IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, @cell = "N/A") = 4, "N/A", IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, OR(@cell = "No", @cell = "N/A")) = 4, "No", IF(COUNTIFS([Criteria A]@row:[Criteria D]@row, OR(@cell = "Yes", @cell = "N/A")) = 4, "Yes", "Sometimes"))))

cheers

Kelly

• Options

Hi Kelly,

Thank you very much. Works like a charm. Is there a way to modify this formula such that it will take any number of rows using one formula. At present 4 means for four rows, but there may be 3, 5 or six rows. Is it possible to create 1 such formula that will work regardless of the number of rows?

• Options

Just found out. if I inserted all four N/As its displaying "No", when it should be "N/A". can this be amended also?

• ✭✭✭✭✭✭
Options

Hey Vinton,

I believe you mean the '4' refers to 4 columns instead of 4 rows. The range in your current formula is looking across a single row from Criteria A to Criteria D (4 columns) for specific terms. The best I can answer right now is 'it depends'. A screenshot and more details on your question will help the community determine if what you're asking is possible for your situation.

Kelly

• ✭✭✭✭✭✭
edited 09/05/22
Options

I just tested and I cannot replicate 4 N/A's producing a 'No' answer. My sheet produces a 'N/A' with 4 N/A's.

• Options

Hi Kelly,

Thanks again. The formula works well. It was an error on my path. Much appreciated.

• Options
• ✭✭✭✭✭✭
Options

Do you want to show us your sheet so we can work on your question about dynamic ranges and counts?

Kelly

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!