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

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

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)

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.

Thank you! That really helps. I was trying to see how to use that function and was having difficulty.

Happy to help. 👍️

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.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 68 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!