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

Answers

  • 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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • What is the difference between:

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

    =COUNTIFS(range:range,="")

    ?

    I am unfamiliar with the @cell deal. thx!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!