How to Count Blank or Non Blank Cells in Excel

by

In this tutorial we will learn how to use Excel’s built-in functions to count blank cells, non-blank cells, and counting blank cells that meet certain criteria. We will be using the functions COUNTBLANK, COUNTA, and COUNTIFS to achieve these tasks.

Counting blank cells. We have a list of data with some containing names and some containing none. We want to count all the cells that are blank. To do this, we will use the COUNTBLANK function. Here’s a step-by-step guide in using the COUNTBLANK function:

  1. Select the cell you want to count the blank cells in.
  2. On the cell, type in “=COUNTBLANK” followed by an open parenthesis.
  3. Select the range of cells for which you wish to count. In this case, A2:A9.
  4. Close the parentheses and press Enter. The formula should look like this: =COUNTBLANK(A2:A9).
  5. The function will now calculate the number of blank cells in the selected range and the result will be displayed in the cell where you entered the function.

Counting non-blank cells. In the same example, we want ot count the items that are not blank. To do this, we will use the COUNTA function. Here’s a step-by-step guide in using the COUNTA function:

  1. Select the cell you want to count the cells in.
  2. On the cell, type in “=COUNTA” followed by an open parenthesis.
  3. Select the range of cells for which you wish to count. In this case, A2:A9.
  4. Close the parentheses and press Enter. The formula should look like this: =COUNTA(A2:A9).
  5. The function will now calculate the number of cells in the selected range that have data in them, and the result will be displayed in the cell where you entered the function.

Counting blank cells that meet a certain criteria. In this example, there is a list of products and the quantity of sales. We want to find a specific product that didn’t garner any sales. To do this, we will use the COUNTIFS function. Here’s a step-by-step guide to using the COUNTIFS function:

  1. Click on the cell where you want to place the result of the COUNTIFS function.
  2. Type “=COUNTIFS(” in the selected cell.
  3. We will then add in the first criteria, which is the specified product by selecting the range of products.
  4. Then, add a comma and type in the specific product. In this case, Chips.
  5. Next, we will add the criteria to count the empty sales. Type in a comma, then choose the range of quantities sold.
  6. Type another comma and then insert two quotation marks (“”) as the criteria to count cells with zero sales.
  7. Press the Enter key to count how many times chips that didn’t garner any sales.

Conclusion

Excel’s built-in functions COUNTBLANK, COUNTIF, and COUNTIFS make it easy to count blank cells, non-blank cells, and cells that meet certain criteria. With these functions, you can analyze your data and gain insights that can help you make better decisions for your business or project.

Related Posts: