Adding a condition to recognize if the external reference cell has any data or no data at all
I have a metrics sheet that pulls data from another sheet. I want the formula to mirror the way the Filter feature works to say "if this cell has any data whatsoever in it (in this case any name) then count it. if the cell has no data, then don't count it."
This is how I have started my formula:
=COUNTIFS({Post Closing Processor}, {Status}, ="Closed")
I want it to recognize only cells where the Post Closing Processor column has a name in it "if" the Status column for the same row reads "closed".
Any help would be greatly appreciated!
Best Answer
-
You were pretty close! Try this:
=COUNTIFS({Post Closing Processor}, ISTEXT(@cell), {Status}, "Closed")
The ISTEXT function tests if a cell value is text or not. This would exclude cells with date or numeric values. (@cell) tells the system to evaluate each cell in the range. ISTEXT(@cell) says evaluate each cell in the range to see if it is text.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
You were pretty close! Try this:
=COUNTIFS({Post Closing Processor}, ISTEXT(@cell), {Status}, "Closed")
The ISTEXT function tests if a cell value is text or not. This would exclude cells with date or numeric values. (@cell) tells the system to evaluate each cell in the range. ISTEXT(@cell) says evaluate each cell in the range to see if it is text.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
That works. Thank you so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!