Check Sheet Logo
Check Sheet

COUNTBLANK

The COUNTBLANK formula is a mathematical formula that counts the number of blank cells within a given range. It is commonly used for data analysis and quality control.

Usage

Use the COUNTBLANK formula with the syntax shown below, it has 1 required parameter:

=COUNTBLANK(range)
Parameters:
  1. range (required):
    The range of cells that you want to count the blank cells from.

Examples

Here are a few example use cases that explain how to use the COUNTBLANK formula in Google Sheets.

Counting blank cells in a data set

If you have a large data set with missing values, you can use COUNTBLANK to quickly count the number of blank cells and assess the quality of the data.

Checking for data entry errors

By using COUNTBLANK in combination with other formulas, you can check for data entry errors and inconsistencies in your spreadsheet.

Calculating completion percentages

If you have a list of tasks or items that need to be completed, you can use COUNTBLANK to calculate the percentage of completion by subtracting the number of blank cells from the total number of cells.

Common Mistakes

COUNTBLANK not working? Here are some common mistakes people make when using the COUNTBLANK Google Sheets Formula:

Not providing a range argument

One common mistake is forgetting to provide a range argument to the COUNTBLANK formula. This will result in an error or an incorrect result. To correct this mistake, make sure to include a range argument that specifies the range of cells you want to count.

Including non-blank cells in the range argument

Another common mistake is including non-blank cells in the range argument. This will result in an incorrect result since COUNTBLANK only counts blank cells. To correct this mistake, make sure to only include the range of blank cells you want to count.

Using a range that includes multiple columns or rows

COUNTBLANK can only count blank cells within a single column or row. If you provide a range that includes multiple columns or rows, the formula will return an error or an incorrect result. To correct this mistake, make sure to only include a single column or row in the range argument.

Using a range that includes non-numeric values

COUNTBLANK only counts blank cells that contain numeric or date/time values. If you provide a range that includes non-numeric values, such as text or boolean values, the formula will return an error or an incorrect result. To correct this mistake, make sure to only include a range of blank cells that contain numeric or date/time values.

Misunderstanding what the formula does

One common mistake is misunderstanding what the COUNTBLANK formula does. Some users may expect the formula to count cells that are not blank, or to count cells based on a specific condition. However, COUNTBLANK only counts blank cells. To avoid this mistake, make sure to understand the purpose of COUNTBLANK and how it works.

The following functions are similar to COUNTBLANK or are often used with it in a formula:

  • COUNTA

    The COUNTA function counts the number of non-empty cells within a specified range. It can take up to 30 arguments which can be individual cells or ranges. This function is commonly used to count the number of entries in a list or to determine the number of cells that contain data.

  • COUNTIF

    The COUNTIF formula counts the number of cells within a specified range that meet a certain criterion. This formula is commonly used to count cells that meet a specific condition or criteria.

  • SUM

    The SUM function in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.

  • AVERAGE

    The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.

Learn More

You can learn more about the COUNTBLANK Google Sheets function on Google Support.