COUNTA Function
Calculates the number of non-blank cells within a given set of values
Calculates the number of non-blank cells within a given set of values
The COUNTA Function is categorized under Statistical functions and will calculate the number of cells that are not blank within a given set of values. The COUNTA function is also commonly referred to as the Excel COUNTIF Not Blank formula.
As a financial analyst, the COUNTA function is useful if we wish to keep a count of cells in a given range. Apart from crunching numbers, we often need to count cells with values. In such scenario, the function can be useful.
=COUNTA(value1, [value2], …)
The Excel countif not blank formula uses the following arguments:
To understand the uses of the COUNTA function, let us consider a few examples:
Suppose we are given the data below:
As seen above, the COUNTA function will count text or formula errors. So unlike the COUNT function, which considers only numbers, COUNTA considers numbers, dates, text values, logical values, and errors).
We get the results below:
The COUNTA function can be used for an array. If we enter the formula =COUNTA(B5:B10), we will get the result 6, as shown below:
Suppose we wish to count the number of cells that contain data in a given set, as shown below:
To count the cells with data, we will use the formula =COUNTA(B4:B16).
We get 8 as the result, as the COUNTA function will not count cells that are absolutely empty cells.
Suppose we wish to count cells that are not equal to a range of certain things. We can use a combination of the COUNTA, COUNTIF and SUMPRODUCT functions to get the desired results.
Suppose we are given the data below:
To count cells not equal to Rose and Marigold, we used the following formula:
We can list down the things we wish to exclude from counting. One other way to arrive at the same result is to use the formula =COUNTIFS(B4:B9,”<>Rose”B4:B9,”<>Marigold”).
However, the above formula would be tedious to use if the list contains many items. We need to add an additional range/criteria pair for each item that we don’t wish to count.
Hence, we can use a reference list and use the =COUNTA(B4:B9)-SUMPRODUCT(COUNTIF(B4:B9,D5:D6)) formula.
The formula uses the range D5:D6 to hold values that we don’t want to count. The formula starts by counting all values in the range being counted with COUNTA. Next, it generates a count of all things we don’t want to count with COUNTIF. Lastly, SUMPRODUCT will sum up all items in the array, which returns 2. The number is then subtracted from the original total to yield the final result.
We get the result below:
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!