We can use COUNT, COUNTA, COUNTIF, COUNTIFS, QUERY, SUMPRODUCT, etc., functions to count if a cell is not blank in Google Sheets.
For example, I have employee names in cell range A2:A and B2:B for entering the number of days they were present in January.
I’m filling this column as and when I get the report from my timekeeper.
I want to check how many non-empty cells are in B2:B that correspond to A2:A. So I can ask him to submit balance attendance details.
In this particular scenario, I can use the COUNT function. If the values are text formatted, then use COUNTA.
Must Check: How to Use All Google Sheets Count Functions [All 8 Count Functions]
But in some scenarios, we may conditionally count non-empty cells in a range in Google Sheets.
For example, we have item names in column A and sales volumes in column B.
Assume “apple” is an item in that list in column A.
We can use COUNTIF to find how many times the item “apple” appears in column A as it accepts the criterion.
But how do we count the number of occurrences of “apple” with sales volume >0?
We will see all in this COUNT IF NOT BLANK tutorial. But before going into the details, one more thing.
When you apply count or count if not blank in Google Sheets, the formula output may not match your physical count of non-blank cells.
There are two main reasons that I could point out.
- One is the chances of space, apostrophes, or hidden characters in the array or range.
- Or you may be using a formula that returns a null or empty string in the count/count if range, for example,
IF(A1="apple", 100,"")in cell B1. If the A1 value is not the text “apple,” the formula will return a null string. It returns FALSE when you test B1 with ISBLANK.
How to Return TRUE Blank Cells in a Formula?
This section will help you get correct results when you count non-blank cells in Google Sheets. Applicable to Countif non-blank also.
Returning TRUE blank cells depends on the formula in use.
In logical functions such as IF, IFS, IFNA, and IFERROR you can leave specifying the value if false, i.e., the second argument part.
Return TRUE Blank Cells in Logical Functions:
Instead of =IF(A1="apple",100,""), use the below formula.
=IF(A1="apple",100,)When using IFERROR like =IFERROR(your_formula,"") use it like =IFERROR(your_formula,)
Tips
1. Specifying TRUE Blank Cells in DATABASE Functions:
When you want to specify blank criteria (two vertical blank cells) in database functions, you can use {if(,,);if(,,)} or VSTACK(if(,,),if(,,)).
In this if(,,) returns one truly blank cell. An example of this usage is the Row-Wise Multiplication of a 2-D Array in Google Sheets.
2. When Using LAMBDA Functions:
In LAMBDA helper functions (LHF) SCAN and REDUCE, you may want to specify one blank cell in the initial_value argument part.
There you can follow the logical function approach or the database function approach. I mean, either leave using initial_value or specify if(,,).
E.g. =scan(if(,,),A2:A,Lambda(a,v,a+v)) (Running Total formula)
Let’s go to count a range if not blank in Google Sheets.
Counting Non-Blank Cells in a Range in Google Sheets
We will start with the most basic one.
COUNT, COUNTA, or COUNTIF Function: Counting All Non-Blank Cells
In the following example, you can find employee names in the cell range A2:A10 and their attendance details in cell range B2:B10.
The following COUNT function in cell D2 returns the count of nonblank cells in the range B2:B10.
=COUNT(B2:B10)
Here is an alternative using COUNTIF.
=COUNTIF(B2:B10,"<>")How do I choose the correct function for such a count?
In simple terms, use COUNTA in a text column and COUNT in a date, time, or numeric column.
To count non-blank cells in a mixed data type column, use COUNTA. But the COUNTIF works in all the scenarios.
How do we exclude a particular value from the count?
Here comes the relevance of the count if not blank in Google Sheets. Before coming to that we must know how to deal with invalid count results due to spaces, null values, etc.
QUERY, COUNTIF, or SUMPRODUCT Function: Counting Real Non-Blank Cells
QUERY is no doubt one of the magical functions in Google Sheets.
Excel users were widely using SUMPRODUCT for turning conventional formulas into arrays.
Other than these two, we can use COUNTIF for counting ‘real’ non-blank cells in Google Sheets.
If a cell has a visible value when we apply two different colors for text and cell background, that is a real/true nonblank cell.
But all three require the help of the TRIM function to return the count of only truly non-empty cells in Google Sheets. Further, they can be helpful in countif not blank in Google Sheets.
In the following examples, I’ve put an apostrophe in cell A5 and a space in cell A8. So the following formula will return 9 when it must be 7.
=COUNTA(A2:A10)Even if you use TRIM with it, the output will be the same. Then what are the available solutions?
SUMPRODUCT: =SUMPRODUCT(LEN(TRIM(A2:A10))>0)
COUNTIF: =ARRAYFORMULA(COUNTIF(LEN(TRIM(A2:A10))>0,TRUE))
Both formulas require the LEN function.
QUERY: =ARRAYFORMULA(QUERY(TRIM(A2:A10),"Select Count(Col1) label Count(Col1)''"))
TRIM removes white space, and LEN returns the length of trimmed text.
When using both these functions in an array/range, we must use an array function such as SUMPRODUCT, INDEX, ARRAYFORMULA, or SORT. I’ve chosen the ARRAYFORMULA.
Count IF Not Blank With Criteria in Google Sheets
We have seen a few examples of how to count cells in Google Sheets. I mean non-blank cells. As a side note, you can use the COUNTBLANK function to count blank cells in Google Sheets.
I prefer the COUNTA function in most cases except counting truly non-blank cells. It’s capable of counting text values and numeric values. The COUNTIF is my next choice.
The count if not blank formula is usually for conditionally counting a column or multiple columns. Here is one such scenario where you can try this.
COUNTIFS Not Blank
I want to count a particular item in one column if the corresponding column is not blank. We can use the COUNTIFS function for this.
=COUNTIFS(A2:A,"orange",B2:B,"<>")It counts “orange” in A2:A if the corresponding cells in B2:B are not blank.
Lambda and Other Options for Count If Not Blank in Google Sheets
The LAMBDA and its helper functions make impossible things possible in Google Sheets.
We can use the MAP lambda helper function for the count if not blank in Google Sheets. Here is the MAP alternative to the above COUNTIFS Not Blank formula in Google Sheets.
=SUM(MAP(A2:A,B2:B,LAMBDA(a,b,--AND(a="orange",b<>""))))What about SUMPRODUCT and QUERY?
Of course, we can use them too.
QUERY: =QUERY(A2:B,"Select Count(A) where A='orange' and B is not null label Count(A)''")
SUMPRODUCT: =SUMPRODUCT(A2:A="orange",B2:B<>"")
That’s all. Any doubts? Please ask me in the comments.
