3 Replies Latest reply: Mar 31, 2013 6:06 PM by Badunit
brucet9 Level 1 Level 1 (0 points)

I want to count the empty cells in a column.

 

The function COUNTIF can find cells with any particular text entered as its second argument, but I can't figure out how to count empty cells.

 

The logical solution would be COUNTIF(B6:B80, ISBLANK) but this lame program says "ISBLANK" is not a valid reference" even though it is a valid function.  Nor is "null", "empty", "blank" or anything else I have tried valid. 


Mac mini, OS X Mountain Lion (10.8.2)
  • 1. Re: how can I get "COUNTIF" to count empty cells?
    brucet9 Level 1 Level 1 (0 points)

    I stumbled on the answer while starting over using the "fx" button (instead of just changing arguments in the previous COUNTIF formula that works for specific text in cells in a range) and finding the function COUNTBLANK.

     

    Problem solved

  • 2. Re: how can I get "COUNTIF" to count empty cells?
    Wayne Contello Level 6 Level 6 (13,615 points)

    isblank() is a function that takes one argument.  so your use of isblank is incorrect because there is no argument.  To get the count of blank cells in a column you need to know two things:

    1) how many cells are in the column and

    2) how many cells have something in them

     

    you can the the count of cells in a column using the function rows()-- no arguments necessary but you can provide a cell or column if you want.

     

    you can get the count of cells with something in them using the function countif()

     

     

    you can combine like this:

     

         Screen Shot 2013-03-31 at 5.55.55 PM.png

    B1=ROWS(C)

    B2=COUNTIF(C, "="&B3)

    B3 <empty>

    B4=B1-B2

     

    A note... B3 should be left blank for this example. 

     

    There is a free, downloadable USers' Guide and a function reference you may find helpful:

    http://support.apple.com/manuals/#productivitysoftware

  • 3. Re: how can I get "COUNTIF" to count empty cells?
    Badunit Level 6 Level 6 (10,815 points)

    Glad you got it figured out. I am not sure why you think a "valid function" is the same as a "valid reference" but I guess that's irrelevant now.