How To Count If Not Blank in Google Sheets? [3 Methods]

I often use Google Sheets for my work, and even with years of experience, I only knew about the COUNTIF method for counting non-empty cells. However, after research, I found there are some other ways to do it.

But then, there are certain pitfalls of these alternative methods. Therefore, if the COUNTIF method seems complicated or you want to explore other ways, you’ve come to the right place.

This article is a detailed step-by-step guide where I will discuss in detail all three methods along with their limitations.

Method 1: Using the COUNTIF Function

The COUNTIF method is usually used to count data based on the information in specific cells. However, you can also use COUNTIF to find not blank cells.

1. Select an empty cell.
2. Enter the formula “=COUNTIF(“.
3. Enter the range you want to count.
4. Enter a comma (,)
5. Use “<>” and press Enter.
   

So, your formula will look like this: =COUNTIF(A4:A10, “<>”). Ensure you use the double inverted commas before you press Enter.

Method 2: Using the COUNTA Function

COUNTA stands for Count all. While it is the most straightforward, it may count a cell as non-empty if that cell contains text strings or numbers.

Therefore, use this formula only if you’re sure that your data doesn’t contain empty strings or apostrophes.

1. Click on an empty cell.
2. Enter “=COUNTA(“
3. Enter the range you wish to count.
4. Press Enter.
   

So, your formula will look like this: =COUNTA(A3:A10).

However, a cell might contain an empty string. This is because of the result of a formula, or many people use apostrophes while entering numbers to show them as text. In this case, the COUNTA formula will count them as non-empty cells and present a false result.

Method 3: Using the SUMPRODUCT Function

SUMPRODUCT is slightly more complicated than the other two. However, it is more reliable when you aren’t sure if your cells contain apostrophes.

Therefore, it’s wise to recount if you think the COUNTA formula isn’t producing satisfactory results.

1. Click on an empty cell.
2. Enter “=SUMPRODUCT(LEN(TRIM(“.
3. Enter the range you wish to count.
4. End the formula with “))>0)”.
5. Press “Enter”.
   

This formula checks if the cell has at least one character. The LEN function ensures the Count of only the cells with a character length greater than 0.

On the other hand, the TRIM function ensures that the cells with space characters are not counted.

Other Basic Count Functions

Not all of us use the COUNTA and COUNTIF methods on a daily basis. Therefore, here are some basic and essential count functions that will elevate your Google Sheets experience for efficient data analysis.

• SUM: Adds up the selected range of rows and columns.
• Average: Shows the average of the selected range.
• MIN: Identifies the minimum value and shows the smallest value of a range.
• MAX: Identifies the maximum value and shows the largest value of a range.
• IF: Aids conditional calculations and returns one value if one condition is true and the other is false.
• IFERROR: Helps in identifying errors in calculation by returning to a custom result when a formula generates an error.
• Not Equal: It is a multi-functional operator that allows for filtering values, validating data, and more.

Frequently Asked Questions

Wrap Up!

I often need to count non-empty cells in Google Sheets, and it offers excellent formulas for quick analysis of a dataset. While you can do this manually, it’s best to rely on the COUNT formulas when analyzing more extensive data sets.

However, if you’re not a regular user, you might find these formulas slightly confusing. Take your time to understand which method works best for you.

Once you get the hang of it, you’ll never return to manual calculating not empty cells, and save effort and time.