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")))
Answers
-
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
-
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?
-
Just found out. if I inserted all four N/As its displaying "No", when it should be "N/A". can this be amended also?
-
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
-
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
-
Hi Kelly,
Thanks again. The formula works well. It was an error on my path. Much appreciated.
-
Do you want to show us your sheet so we can work on your question about dynamic ranges and counts?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!