How to count cells with BLANK value in a column

Say that I want to count how many cells with BLANK in a column? The column have many NOT BLANK values also. eg. ([Col]2 with BLANK value)

Col

row_1 a

row_2

row_3 a

....

row_n a

so the column has 1 cell with BLANK, the count will be 1.

How to write a formula to get the count?

Thanks!

Find more posts tagged with

Formulas

Comments

Andrée Starå

Hi,

Here are two different options.

Try something like this.

=COUNTIF(Col:Col; "")

The same version but with the below changes for your and others convenience.

=COUNTIF(Col:Col, "")

Or this

=COUNTIF(Col:Col; ISBLANK(@cell))

The same version but with the below changes for your and others convenience.

=COUNTIF(Col:Col, ISBLANK(@cell))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Hawk

Hi Andree,

Thanks!

COUNTIF(COL:COL,"") works, but COUNTIF(COL:COL,ISBLANK@cell) does not work. what's the meaning of "@cell"? Do I need to change it to other format?

Andrée Starå

Happy to help!

Both options should work? Do you get an error message?

@cell checks each cell in the range.

More info: https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Best,

Andrée

Hawk

Andree,

Thanks, @cell works now!

Andrée Starå

Excellent!

Happy to help!

Why didn't it work before?

Best,

Andrée

Hollie Green

I am getting an #unparsable error below is my formula and all of the cells are blank can you advise what I have typed incorrectly.

=COUNTIF([OEM]:[OEM], ISBLANK(@cell), [Tech]:[Tech], ISBLANK(@cell), [Scheduling]:[Scheduling], ISBLANK(@cell), [Cust Care Chat]:[Cust Care Chat], ISBLANK(@cell), [Cust Care E2C]:[Cust Care E2C], ISBLANK(@cell), [Cust Care Inbound]:[Cust Care Inbound], ISBLANK(@Cell), [Customer Care]:[Customer Care], ISBLANK(@cell), [R&R]:[R&R], ISBLANK(@cell))

Genevieve P.

Hi @Hollie Green

You're using COUNTIF (singular), however since you have multiple columns to check, you'll want to use COUNTIFS (plural).

Let me know if that fixed it for you! 🙂

Hollie Green

I did not catch that I missed the S but it is still giving the same error with the countifs

Genevieve P.

Hi @Hollie Green

I see that in one of your ISBLANKs you have @cell written as @Cell with a capital - this will cause the formula to error as well!

May I suggest using "" for "equals blank" instead of ISBLANK(@cell)? It may be easier to review for errors:

=COUNTIFS([OEM]:[OEM], "", [Tech]:[Tech], "", [Scheduling]:[Scheduling], "", [Cust Care Chat]:[Cust Care Chat], "", [Cust Care E2C]:[Cust Care E2C], "", [Cust Care Inbound]:[Cust Care Inbound], "", [Customer Care]:[Customer Care], "", [R&R]:[R&R],"")

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Trending discussions

Pulling in data from 1 sheet to another isn't working with any function.
PMO Template - I want to pull in the Project Name Column from the Project Metadata sheet into my custom Attendance Sheet > Project Name Column. I've tried VLOOKUP, INDEX MATCH, JOIN COLLECT, I've changed parenthesis to brackets. I am new to Smartsheets and can't get anything to work. I've watch videos, followed community…
Help With If Count If to Identify Unique Values in a Column
I'm trying to make a helper column to identify if a new/unique project code has been entered in a column so that I know when a new project has been created. I've been trying to set up this formula and plan to then set up an automation to be notified when "New Project Code" has been entered into a cell. It seems to be ok on…
Logging separate data points of the same type of data for one row
Looking for a way to easily log and reference multiple data points for the same type of data (ex: successful phone contacts) that all show for one entry (by member name). Want this to be both easy to enter the information (ideally by interacting with the same row via Dynamic View), and easy to see data on the back end of…