How to Cells with Empty String

David

2 years ago

Some cells contain empty strings, which appear as empty quotation marks (""). These cells are defined as blank, but they contain formulas.

The COUNTBLANK and COUNTIF functions in formula 67 count them as empty. However, you can use the formula COUNTIF(A2:C12,"=") to exclude them.

Some cells contain empty strings, which appear as empty quotation marks (""). These cells are defined as blank, but they contain formulas.

The COUNTBLANK and COUNTIF functions in formula 67 count them as empty. However, you can use the formula COUNTIF(B2:C12,"=") to exclude them.

Example: You are working with a worksheet with the products in column A, the date sold in column B, and the amount sold in column C. Cell C8 and C9 are empty string cells.

Question: How many are blank cells, excluding empty strings in the range B2:C12?

You can use the COUNTIF function with the equal sign ("=").

=COUNTIF(B2:C12,"=")

The result returns 11. Cells C8 and C9 are empty strings and they are excluded.

You can also get the same result with the following formula:

=ROWS(B2:C12)*COLUMNS(B2:C12) – COUNTA(B2:C12)

Explanation:

Step 1: ROWS(B2:C12): To count the number of rows in the range;
Step 2: COLUMNS(B2:C12): To count the number of columns in the range;
Step 3: ROWS(B2:C12)*COLUMNS(B2:C12): To count the number of cells in the range;
Step 4: COUNTA(B2:C12): To count the number of cells that are non-blank in the range;
Step 5: ROWS(B2:C12)*COLUMNS(B2:C12) – COUNTA(B2:C12)