Trying to ignore blank rows in a linked sheet

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

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

Hey @Vinton Douglas

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

Vinton Douglas

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?

Vinton Douglas

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

Kelly Moore

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

Kelly Moore

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.

Please share the formula in your cell

Vinton Douglas

Hi Kelly,

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

Vinton Douglas

Kelly Moore

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

Kelly

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.