Counting Blank Cells

I need assistance with counting blanks in the due date column in my smartsheet.  I used the formula =IF(ISABLANK({National Alliance Operation Inquiries Range 2}1),"BLANK", "NOT BLANK") but it seems not to work. Screenshots have been attached for your review.


Best Answers

«134

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Beronica,

    This formula seems to be returning if one cell is either blank or not, is that correct?


    If you're looking to do a count in a column of how many blank cells there are, you can try the following:

    =COUNTIF({National Alliance Operation Inquiries Range 2}, ISBLANK(@cell))


    If you're working in the same sheet, you can check to see if a cell is blank and return the answer in the same row by using the following:

    =IF(ISBLANK([Column in Sheet]@row), "BLANK", "NOT BLANK")


    Then you could use a cross-sheet COUNTIF formula to count how many of the cells say "BLANK", if that's what you're going for. If neither of these work for you, it would be very useful to see the screen captures you mentioned.

    Thanks!

    Genevieve

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this...

    =COUNTIFS({National Alliance Operation Inquiries Range 2}, "")

  • Genevieve P.
    Genevieve P. Employee Admin

    Oh goodness, of course! @Paul Newcome coming in with the simple save.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Genevieve P Haha. We must have been typing at about the same time. When I first opened the post it was still unanswered.

    If it makes you feel any better... My initial thought for something like this is always an ISBLANK function. Even after all of the formulas written and time spent I still have to remind myself that "" is the same thing as blank and saves a few keystrokes (not to mention cuts out a set of those pesky parenthesis).

  • Beronica Muller
    Beronica Muller ✭✭✭✭

    Thank you both! :-) It seems like the formula is counting more cells than are in that column.  For example, I exported the spreadsheet to compare the blanks and the spreadsheet has 83 blank cells when the formula returned 93. I am trying to attach screenshots but it just keep saying loading......

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    There are by default 10 rows added to the bottom of every sheet. It is probably grabbing those. Let's try subtracting 10...

    =COUNTIFS({National Alliance Operation Inquiries Range 2}, "") - 10

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Beronica,

    I agree with what Paul said - it sounds like this may be happening because it's counting the blank rows at the very bottom of the sheet, past any rows that currently contain data in other columns.

    Would you be able to explain a little further what you're looking to do, or what the scenario is? A helper column indicating when a cell is blank, but only if the row has other information, may be another way to go (then you can count that helper column instead of just blank cells).

    For the images, could you try adding them as images embedded in with the text in this text box (versus attaching them as attachments)? You can drag-and-drop images into the box.

    Thanks!

  • Beronica Muller
    Beronica Muller ✭✭✭✭

    The formula that Paul provided worked.  I wanted a formula to count all of the blank cells that are in the due date column for each ticket that have logged. 

    Thanks for the advice on adding screenshots as well.

  • Genevieve P.
    Genevieve P. Employee Admin

    Ahhhh! Yes, @Paul Newcome!!! Great solution.

    @Beronica Muller so glad that Paul's formula worked for you. 🙂

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Genevieve P Thank you! Not to say your solution was wrong. Just that there were multiple ways of accomplishing it. I personally try to only add columns when necessary, but I have worked with other people that love adding in helper columns because the formulas themselves are generally a little less complex.

  • Beronica Muller
    Beronica Muller ✭✭✭✭

    One more question guys, is there a way to make the font size smaller for the numbers in a pie graph?  I know that we can change the font and size for the title but not the size of the numbers.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Not in the graph itself. It would be great if we could though. Feel free to Submit a Product Enhancement Request when you have a moment.

  • Beronica Muller
    Beronica Muller ✭✭✭✭

    Awesome!  Will do..


    Thanks Paul!

  • Beronica Muller
    Beronica Muller ✭✭✭✭

    Paul,

    I know that I am being "extra" today but my management has tasked me with another scenario. How can I incorporate the blanks into a formula that is already calculating days? For example, I have formula =COUNTIFS({National Alliance Operation Inquiries Range 1}, <(8)) that is used to calculate the number of inquiries that are pending under 8 days.  Since the blanks have not been assigned and are in the 8 day window, I will need to calculate that in my total. 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!