## COUNTIF not blank cells

*Article last updated on November 28, 2017*

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.

### Download excel *.xlsx file

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 […]

Use dynamic ranges to automatically add new values

This post demonstrates how to automatically add new values to a drop down list and a chart. This tutorial contains […]

How to create a dynamic pivot table and refresh automatically

David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]

Create a drop down list containing alphabetically sorted values

Overview This article describes how to create a drop down list populated with sorted values from A to Z. The […]

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Add or remove a value in a drop down list

Cell B3 contains a drop down list (Data Validation). The values in drop down list are from column H. I […]

Lookup with an unknown number of criteria

Rashid asks: I used your array formula with great success to find the search results from multiple critera. However my […]

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 […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

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 […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Create a unique distinct list and sort based on frequency

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

Compare two columns for same values [Excel Formula]

Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

Extract largest values from two columns

Question: How do I extract the five largest values from two or more columns? Answer: Formula in A12: =INDEX(tbl, SMALL(IF(LARGE(tbl,ROW(A1))=tbl, […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form