How to count cells with BLANK value in a column

Hawk

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!

 

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