If Cell is Empty or Blank
Hi,
I have created the following formula
=IF(COUNTIF([New Case Checklist Status]@row:[PCORI Reports]@row, "Pending") > 0, "Pending", IF(COUNTIF([New Case Checklist Status]@row:[PCORI Reports]@row, "Created") > 0, "Pending", IF(COUNTIF([PCORI Reports]@row, "Requested"), "Pending", "Done")))
which is working efficiently, but problem occurs when the cell is empty, and it show me return "Done". As my formula is saying "Put done if the values doesn't have "pending", "created", or "requested". So it is taking an empty cell and showing "done"
How can I use ISBlank formula to put Empty cell or "-"
Thank you!
Best Answers
-
Do you want it to be blank if ALL are blank? If so, try this...
=IF(COUNTIFS([New Case Checklist Status]@row:[PCORI Reports]@row, <> "") > 0, IF(COUNTIF([New Case Checklist Status]@row:[PCORI Reports]@row, "Pending") > 0, "Pending", IF(COUNTIF([New Case Checklist Status]@row:[PCORI Reports]@row, "Created") > 0, "Pending", IF(COUNTIF([PCORI Reports]@row, "Requested"), "Pending", "Done"))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
To use the ISBLANK function across a range you actually have a few different options.
=IF(COUNTIFS(range:range, ISBLANK(@cell)) > 0,
=IF(COUNTIFS(range:range, @cell = "") > 0,
=IF(ISBLANK(JOIN(range:range)),
=IF(JOIN(range:range) = "",
and probably a few other options/variations that I didn't think of right off.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Do you want it to be blank if ALL are blank? If so, try this...
=IF(COUNTIFS([New Case Checklist Status]@row:[PCORI Reports]@row, <> "") > 0, IF(COUNTIF([New Case Checklist Status]@row:[PCORI Reports]@row, "Pending") > 0, "Pending", IF(COUNTIF([New Case Checklist Status]@row:[PCORI Reports]@row, "Created") > 0, "Pending", IF(COUNTIF([PCORI Reports]@row, "Requested"), "Pending", "Done"))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Yes correct! That was my intention. Aha! Thank you so much! I realized my error was that I needed to use COUNTIFS instead of COUNTIF. Thank you
-
COUNTIF vs COUNTIFS doesn't actually make a difference in this case. I use COUNTIFS out of habit. The solution provided actually counts how many cells within the range are not blank (<> ""). If that count is greater than zero (meaning there is at least one cell that is not blank) then we run your original IF statement. Otherwise leave blank.
=IF(COUNTIFS(not blank) > 0, run remaining IFs)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ahh I see! Originally, I was trying to use the ISBLANK function, but was having difficulty because you can't use that function for ranges across multiple columns.
-
To use the ISBLANK function across a range you actually have a few different options.
=IF(COUNTIFS(range:range, ISBLANK(@cell)) > 0,
=IF(COUNTIFS(range:range, @cell = "") > 0,
=IF(ISBLANK(JOIN(range:range)),
=IF(JOIN(range:range) = "",
and probably a few other options/variations that I didn't think of right off.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you! That really helps. I was trying to see how to use that function and was having difficulty.
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
What is the difference between:
=COUNTIFS(range:range,@cell="") and
=COUNTIFS(range:range,="")
?
I am unfamiliar with the @cell deal. thx!
-
@Catherine Hall In this particular instance the only difference is personal opinion.
@cell = ""
and
= ""
will both work exactly the same. I just prefer to use the @cell reference so that it is easier for me to read. "Look across the range and count how many cells equal blank". It is just a habit I got in because there are some instances where if you do not specify @cell then the function will evaluate the entire range as if it is one piece instead of looking at each individual cell.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!