Count Blank and Filled Cells of a Range as One

After reading this article you will be able to count rows at once that contain Text (Non Blank) and blank (both) in a given Data set. If you want to know read how many cells contain only text.

Query

We have two columns Product and Detail. Our task is to find how many Product is Sold and Blank ones.

Formula to implement:

=COUNTBLANK(C3:C13)+COUNTA(C3:C13)

Download Your Example Excel File

Count all cells that are empty and with text in a column.

Functions applicable:

We will be creating a COUNTBLANK and COUNTA formula to count text and blank cells in Excel.

COUNTBLANK: The Excel COUNTBLANK function returns the count of blank cells within the reference arguments ignoring number and text cells. 

COUNTA: The Excel COUNTA function returns the count of numbers and text within the reference list of arguments ignoring blank cells.

Formula Explanation (step by step)

We can easily evaluate formula using F9 key.

Let’s break the formula into multiple small steps to understand its application.

First formula:

COUNTBLANK(C3:C13)

Syntax: =COUNTBLANK(range)

Step 1:

Result: 7         ‘ Zero indicating blank

Returns counts all cells that are blank in the given range (C3 to C13).

Second formula:

COUNTA(C3:C13)

Syntax: COUNTA(value1, [value2], …)

Step 2:

Result: 4

It counts all text containing cells in the range (C3 to C13).

Add first two formula in step 3

Step 3: COUNTBLANK(C3:C13)+COUNTA(C3:C13)

Result: 11

It adds both blank and text cells in column (C) returns count at once.