How to use the COUNTIF function to count not blank cells
The COUNTIF function is very capable of counting non-empty values. Column B above have a few blank cells, they are in fact completely empty.
The first argument in the COUNTIF function is the cell range where you want to count matching cells to a specific value, the second argument is the value you want to count.
In this case, it is "<>" meaning not equal to and then nothing, so the COUNTIF function counts the number of cells that are not equal to nothing.
The COUNTA function is even easier to use, you don't need to enter more than the cell range in one argument. The COUNTA function is designed to count non-empty cells.
There are, however, situations where the COUNTIF and COUNTA function return unexpected results if you are not aware of how they work.
There are blank cells in column C in the picture above that look empty but they are not. Column D shows what they actually contain and column E shows the character length of the content.
Cell C5 and C9 contain a formula that returns a blank, both the COUNTIF and the COUNTA function count those cells as non-empty.
Cell C8 has two space characters and cell C12 has one space character, column E reveals their existence. The COUNTIF and the COUNTA function count those cells as non-empty as well.
The following formula counts all non-empty values in cell range C3:C13 except formulas that return nothing. It checks if the values in cell range C3:C13 are not equal to nothing.
The SUMPRODUCT function can't sum boolean values so I multiply with one to create an array containing 0's (zero) and 1's. In this case, the logical expression counts cells that contain space characters but not formulas that return nothing.
becomes
SUMPRODUCT(({TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE})*1)
becomes
SUMPRODUCT({1;1;0;1;1;1;0;1;1;1;1}) and returns 9 in cell B16.
The formula above in cell C16 counts only non-empty values, it considers formulas that return nothing to be blank and space characters to also be blank. This is made possible by the TRIM function that removes leading and ending space characters.
becomes
=SUMPRODUCT(({"Green"; "Blue"; ""; "Red"; "Cyan"; ""; ""; "Yellow"; "Orange"; ""; "Brown"}<>"")*1)
becomes
=SUMPRODUCT(({TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE})*1)
becomes
=SUMPRODUCT({1; 1; 0; 1; 1; 0; 0; 1; 1; 0; 1}) and returns 7.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
Create a drop down list containing alphabetically sorted values
This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list […]
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]
How to use the COUNTA function
The COUNTA function counts the non-empty or blank cells in a cell reference. The picture above demonstrates the COUNTA function […]
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
How to create a list of random unique numbers
Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
Extract shared values between two columns
Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]
How to use the COUNTIF function
Counts the number of cells that meet a specific condition.
The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]
Use IF + COUNTIF to perform numerous conditions
The COUNTIF function allows you to construct a small IF formula that carries out plenty of logical expressions. Combining the IF […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
