If Cell is Empty or Blank

Tyra Skinner · 08/17/20

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!

Paul Newcome · 08/17/20

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

Paul Newcome · 08/17/20

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.

Paul Newcome · 08/17/20

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

Tyra Skinner · 08/17/20

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

Paul Newcome · 08/17/20

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)

Tyra Skinner · 08/17/20

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.

Paul Newcome · 08/17/20

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.

Tyra Skinner · 08/17/20

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

Paul Newcome · 08/18/20

Happy to help. 👍️

Catherine Hall · 09/14/20

https://community.smartsheet.com/discussion/comment/259986#Comment_259986

What is the difference between:

=COUNTIFS(range:range,@cell="") and

=COUNTIFS(range:range,="")

?

I am unfamiliar with the @cell deal. thx!

Paul Newcome · 09/15/20

@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.

If Cell is Empty or Blank

Best Answers

Answers

Help Article Resources

Categories