percentage of blank cells/non blank cells in a column.

I am looking for a formula that can show me a percentage of blank cells/non blank cells in a column. 

Ex: My Column is titled: New IP/URLs Updated.

   There are 223 cells in the column

    Only 64 of the cells have the word "updated"

I would like a formula that can show this is column has 28.25% updated based on how many blank cells there are to how many are the word "updated."

Is this possible? Thanks!


  • KPH
    KPH ✭✭✭✭✭✭

    Hi @Wendell ONeal

    It is possible.

    You would need a cell that is formatted as %.

    In that cell you can enter a formula that calculates:

    count of "updated" divided by count of rows*

    To find the count of "updated" you can use a COUNTIF function:

    =COUNTIF([New IP/URLs Updated]:[New IP/URLs Updated],"updated")

    *To find the count of rows I like to count the rows where another column in the dataset is not blank. This removes any issues around including rows at the end of the sheet that are entirely blank. So if you have another column that is always populated, such as an ID, count that one. In my example, I'll call this "ID". This COUNTIF will count the number of rows where "ID" is not blank:

    =COUNTIF(ID:ID, <>"")

    Then you simply divide one by the other:

    =COUNTIF([New IP/URLs Updated]:[New IP/URLs Updated], "updated") / COUNTIF(ID:ID, <>"")

    In my example, the result is 40% (2/5)