In financial modeling, most of the time you deal with data containing numbers detailing sales figures, expense breakdowns, budgets, etc. Counting numbers is a simple and easy task in Excel. But what if your data contains other than numbers? How do you count cells with a combination of texts and numbers similar to invoices and product codes? How do you count error values such as #REF! and #VALUE! in Excel? Simply put, how to count non blank cells in Excel?

The purpose of the COUNTA Function In Excel is to count non blank cells in Excel including texts, numbers, error values and spaces. This tutorial will answer what COUNTA Function In Excel is, discuss with examples how to count non blank cells in Excel and identify key differences between other COUNT functions in Excel.

Count Non Blank Cells In Excel With COUNTA Function In Excel

The COUNTA function in Excel is, similar to other COUNT functions, is found under the Statistical Formulas.

When you type a function in Excel, it describes the function as you are typing it.

As shown, the COUNTA Function In Excel will count the number of non blank cells in a range of cells. Before we continue any further, let us define what a non blank cell is.

The COUNTA function in Excel counts cells containing any type of value including:

• Numbers
• Dates and Times
• Text
• Error Values
• Boolean values TRUE/FALSE
• Empty text string (“ ”) or cells that look empty but actually contain invisible characters. For  example, cells that contain
  • Space
  • A formula that returns an empty string

In other words, COUNTA function in Excel practically Counts All values or non blank cells. Obviously, it will not count blank or absolutely empty cells.

Syntax of the COUNTA Function In Excel

Where:

The COUNTA function in Excel is useful in the following cases:

• Count the number of suppliers and manufacturers in a list
• Count the number of transactions for a specific date or time frame
• Count the number of paid and unpaid  customer invoices
• Count the number of quantity of old stock for inventory management
• Count the number of errors appearing in a financial model
• Count the number of names of job applicants

These are but a few of the many ways to use the COUNTA function in Excel. Let’s explore common examples of how to count non blank cells in Excel using COUNTA function in Excel.

How To Count Non Blank Cells In Excel

When you input or import a large amount of data in Excel, some cells can be left blank or empty due to a variety of reasons – the related data is unavailable, inconsistent format, etc. Eventually, you need to check and count the data entered in Excel and ignore any blank or empty cells. You can count non blank cells in Excel in many ways, learn using the COUNTA function in Excel with the common examples presented below.

Count non blank cells in Excel using COUNTA Function in Excel for only one range

The list below highlights a business’ product details starting with the product code, market segment, the product’s launch date, and sales performance.

Using the COUNTA function in Excel you can count non blank cells in Excel in Column A, Product Codes. The COUNTA formula =COUNTA(A2:A12) results in 11.

Notice COUNTA function in Excel counted all values (text, numbers, special characters) inside each cell. Imagine if you have rows and rows of data and you need a quick count of non blank cells in Excel, COUNTA function in Excel does the trick.

Count non blank cells in Excel using COUNTA function in Excel for multiple cell ranges

Assume, you want to count non blank cells in Excel for the entire data set or table. Using the COUNTA function in Excel and the COUNT formula =COUNTA(A2:D12), the total count is 44.

COUNTA function in Excel not only counts numbers, texts but it also includes dates and error values such as #REF! error in our table.

But, will the COUNTA function in Excel work if the cells are not adjacent or a non-contiguous range?

Count non blank cells in Excel for non-contiguous cell ranges

COUNTA function in Excel also works in non-contiguous ranges. Assume, you want to count non blank cells in Excel under Column A, Product Codes, and Column D, Sales $ (m). The COUNT formula =COUNTA(A2:A12,D2:D12) results in a total count of 22.

COUNTA function in Excel Counts All Values Contained In the Formula

As we’ve understood by now, COUNTA function in Excel counts non blank cells in Excel regardless if the cells contain text, numbers, dates, errors and empty text strings. For as long as the cells are not completely blank or empty COUNTA function in Excel works.

It is also important to understand that the COUNTA function in Excel also counts all values contained in the formula. To illustrate, using our first example of COUNTA function in Excel for one range, assume we added 4 news values (also called hardcoded values) to our formula; 0, 1, x and y. What will the total count of non blank cells in Excel be? Does the COUNTA function in Excel also count these hard-coded values?

The new COUNTA formula =COUNTA(A2:A12,0,1,x,y) results in 15. The COUNTA function in Excel can also count the hard-coded values; 0, 1, x, and y.

COUNT Function vs COUNTA Function In Excel

The main difference between COUNT function and COUNTA function In Excel is the COUNT formula exclusively counts numbers. Notice the differences in total count from the table presented below.

Comparing the results from the table:

• Notice in Column A the COUNT function only counted 4 non blank cells vs 11 using the COUNTA Function In Excel,
• The COUNT function counted zero non blank cells in Column B (the cells contained all text) vs 11 using the COUNTA Function In Excel,
• The COUNT function resulted in a total count of non blank cells in Excel of 11 in Column C (dates are entered as numbers) the same as the COUNTA Function In Excel, and
• A total count of non blank cells in Excel of only 10 in Column D (completely ignoring #REF! error) vs 11 using the COUNTA Function In Excel.

The main similarity in COUNT function and COUNTA function In Excel is both ignore blank or empty cells. Assume in Column C there are two blank cells (C7 and C11), both COUNT function and COUNTA function In Excel results in the total count of non blank cells in Excel of 9.

Important Reminders on How To Count Non Blank Cells In Excel

• If you need to count non-blank cells, use the COUNTA function In Excel but if you need to count blank or empty cells in Excel use the COUNTBLANK function in Excel[link to pillar page]
• COUNTA function In Excel does not only count contiguous and non-contiguous ranges but also hardcoded values such as =COUNTA(A2:A12,0,1,x,y)
• COUNTA function In Excel is not suitable if you need to count cells that match certain conditions, the COUNTIF or COUNTIFS functions should be used.
• One quick way to check the COUNTA function In Excel is by using the Excel status bar count. The Excel Status Bar shows the total count of cells that have values such as text, numbers, space(s), formula errors, etc. or simply the total count of non blank cells in Excel.