Counting Blank Cells

Beronica Muller · 05/06/20

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.

Paul Newcome · 05/07/20

To expand on @Genevieve P's comment...

You shouldn't need a helper column. You could reference another column that has data in every row in the same COUNTIFS as well...

=COUNTIFS({National Alliance Operation Inquiries Range 2}, "", {Column Containing Text In Every Row, @cell <> "")

Paul Newcome · 06/14/21

@Beronica Muller Double check that the column names are spelled correctly. If all column names are spelled correctly, can you copy/paste the formula directly from the cell to here?

Genevieve P. · 05/07/20

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 · 05/07/20

Try something like this...

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

Genevieve P. · 05/07/20

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

Paul Newcome · 05/07/20

@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 · 05/07/20

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 · 05/07/20

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. · 05/07/20

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!

Paul Newcome · 05/07/20

To expand on @Genevieve P's comment...

You shouldn't need a helper column. You could reference another column that has data in every row in the same COUNTIFS as well...

=COUNTIFS({National Alliance Operation Inquiries Range 2}, "", {Column Containing Text In Every Row, @cell <> "")

Beronica Muller · 05/07/20

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. · 05/07/20

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

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

Paul Newcome · 05/07/20

@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 · 05/07/20

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 · 05/07/20

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 · 05/07/20

Awesome! Will do..

Thanks Paul!

Beronica Muller · 05/07/20

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.

Counting Blank Cells

Best Answers

Answers

Help Article Resources

Categories