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
