How to Count Cells that are Space

If you need to count the number of spaces in a cell, please check here. However, if you need to count the number of cells that are spaces, you can use the COUNTIF function with space as the count criterion.

Example: You are working with a dataset with the first name in column A, the last name in column B and the salary in column C.

Formula 1: To count how many cells that are space in Column A.

= COUNTIF(A2:A8, " ")
Please note that one space is required between the quotation marks.

The result returns 2, and there are two cells in column A that are space: A5 and A8.

Formula 2: To count how many cells that are space in Column C.

= COUNTIF(C2:C8, " ")
Please note that one space is required between the quotation marks.

The result returns 1, and there is one cell in column C that is space: C7.

Formula 3: To count how many cells that are spaces in the range A2:C12.

However, the COUNTIF formula returns those cells that have one space, and you can use the following formula if there is more than one space in the cells.

=COUNTA(A2:C12)-SUMPRODUCT(–(LEN(TRIM(A2:C12))>0))

  • COUNTA(A2:C12): To count the length of the range;
  • TRIM(A2:C12): To remove the extra spaces in each cell;
  • LEN(TRIM(A2:C12): The length of the cells after removing the extra spaces;
  • SUMPRODUCT(–(LEN(TRIM(A2:C12))>0)): The number of cells that are nonblank after removing extra spaces;
  • The difference is the number of cells that are spaces.

Notes: The COUNTIF function

Formula:

= COUNTIF(Range," "), space is required in quotation marks

Cautions:

The formula counts the cells that are space only, not the cells with spaces and other characters.

Practice: Count Cells that are Space

Leave a Reply