How to count number of non-blank cells (cells that contain text)?

Counting Non-Blank Cells in Google Sheets and Excel

Learn how to accurately count cells containing text or numbers, ignoring empty cells, using various formulas in Google Sheets and Excel.

Counting non-blank cells is a fundamental task in spreadsheet analysis. Whether you're tracking data entries, monitoring progress, or cleaning up datasets, knowing how many cells actually contain data (text, numbers, or even errors) is crucial. This article will guide you through different methods to achieve this in both Google Sheets and Microsoft Excel, focusing on formulas that ignore truly empty cells.

Using COUNT and COUNTA for Basic Counts

The simplest way to count non-blank cells involves two primary functions: COUNT and COUNTA. Understanding their differences is key to choosing the right one for your specific needs.

=COUNTA(A1:A10)

This formula counts all non-empty cells in the range A1:A10, including those with text, numbers, logical values, and errors.

COUNTA (Count All) is your go-to function when you want to count any cell that is not entirely empty. This includes cells with numbers, text, dates, logical values (TRUE/FALSE), errors, and even empty strings ("") resulting from formulas. It's the most inclusive counting function for non-blanks.

=COUNT(B1:B10)

This formula counts only cells containing numerical values (including dates and times) in the range B1:B10.

COUNT, on the other hand, is more specific. It only counts cells that contain numerical values. This includes numbers, dates, and times, but it ignores text, logical values, errors, and empty strings. If you specifically need to count only numerical data, COUNT is the appropriate choice.

Visualizing the difference between COUNT and COUNTA

Counting Cells with Specific Content (Excluding Empty Strings)

Sometimes, cells might appear blank but actually contain an empty string ("") as the result of a formula. COUNTA would count these as non-blank. If you want to exclude these 'formula blanks' and only count cells with actual visible content, you'll need a more advanced approach using COUNTIF or SUMPRODUCT.

=COUNTIF(C1:C10,"<>"&"")

This formula counts cells in C1:C10 that are not equal to an empty string. This effectively excludes true blanks and formula-generated empty strings.

The COUNTIF function with the criterion "<>"&"" is a robust way to count cells that contain any actual value, whether it's text or a number, while specifically ignoring cells that are either truly empty or contain an empty string result from a formula. This is often the most desired behavior when you say you want to count 'non-blank' cells.

=SUMPRODUCT(--(C1:C10<>""))

In Excel, SUMPRODUCT can be used similarly to COUNTIF for this purpose. The -- converts TRUE/FALSE values to 1/0, which are then summed.

Counting Text-Only Cells

If your goal is to count only cells that contain text (and not numbers, dates, or errors), you can combine functions like SUMPRODUCT with ISTEXT (in Excel) or ARRAYFORMULA with ISTEXT (in Google Sheets).

Tab 1

In Excel, you can use SUMPRODUCT with ISTEXT:

Tab 2

### Tab 3

=SUMPRODUCT(--ISTEXT(D1:D10))

### Tab 4

Tab 5

This formula checks each cell in D1:D10. ISTEXT returns TRUE for text and FALSE otherwise. The -- converts TRUE to 1 and FALSE to 0, and SUMPRODUCT sums these values.

Tab 6

In Google Sheets, ARRAYFORMULA is often used for array operations:

Tab 7

### Tab 8

=ARRAYFORMULA(SUM(N(ISTEXT(D1:D10))))

### Tab 9

Tab 10

Here, ISTEXT works as in Excel. N() converts TRUE/FALSE to 1/0, and SUM adds them up. ARRAYFORMULA ensures this operation is performed across the entire range.

1. Step 1

Identify your goal: Determine if you want to count all non-empty cells, only numerical cells, or only cells with actual content (excluding formula-generated empty strings).

2. Step 2

Choose the right formula: Use COUNTA for all non-empty cells, COUNT for numerical cells, and COUNTIF(range,"<>"&"") for cells with actual content (excluding empty strings).

3. Step 3

Specify your range: Replace A1:A10 or C1:C10 with the actual range of cells you want to evaluate.

4. Step 4

Verify results: Double-check a few cells in your range to ensure the formula is counting as expected, especially if you have cells with empty strings or only spaces.