Given a set of data, we might want to find out the count of all the Non-blank cells from a particular range.
Excel COUNTA function can be of used to achieve the same.
Let’s see how to do it…!!!
1. What is the COUNTA Function?
2. What is the Syntax?
3. How to use it with an example?
1. What is the COUNTA Function?
The COUNTA function can be used to count the number of cells that are not blank such that numbers, text, logical values, error values, and empty text (“”).
2. What is the Syntax?
=COUNTA(value1,[value2],…)
- value1 = [Required] Can be an item, cell reference, or range within which you want to count.
- [value2] = [Optional] can be up to 255 additional items, cell references, or ranges within which you want to count numbers
Note :
- The numbers, text, logical values, error values, and empty text (“”) will be considered.
- TEXT should be placed in double quotes while the numerics can be passed without quotations.
- Every range that we pass into the formula should have a different number of rows,
- Few times the COUNTA function’s result because differs from what you expect it to be, because an Excel COUNTA formula may count cells that looks empty, but technically they are not. For example, if you accidentally type a space in a cell, that cell will be counted. Or, if a cell contains some formula that returns an empty string, that cell will be counted as well.
3. How to use it with an example?
Let’s have a look at a few scenarios where COUNTA function can be applied.
Let’s find the count…
1. Finding the count of cells which contains non-blank values.
2. Finding the count of cells which contains non-blank values with additional arguments added into it.
3. Finding the count of cells which contains non-blank values and an empty string.
1. Finding the count of cells which contains non-blank values
=COUNTA(B2:B7)
Gives the count of all non-blank cells, neglecting the empty cell in the range.
2. Finding the count of cells which contains non-blank values with additional arguments added into it
=COUNTA(B2:B7,”Sharath”,123,FALSE)
Gives the count of all non-blank cells in the range and includes the additional arguments added, neglecting the empty cell.
3. Finding the count of cells which contains non-blank values and an empty string
=COUNTA(B2:B7,”Sharath”,123,FALSE)
Gives the count of all non-blank cells in the range and includes the additional arguments added, considering the empty string(Since the IF formula here returns an empty string but not a blank).
Hope it has helped you…
