To count the number of cells containing data in a range in Microsoft Excel we will use COUNTA function.
COUNTA:This function is used for count the non blank or occupied cells in a range It is capable of looking thousands of cell values together at once in Microsoft Excel.
Syntax of COUNTA:-
COUNTA(value1, [value2], ...)
Now lets see the various formula types based on the arguments in this function -
| Argument Type | Formula Types | Explanation |
|---|---|---|
| Cell References | =COUNTA(A1, B1) | Count if cells A1 and B1 are occupied |
| Range Reference | =COUNTA(A1:A10) | Count how many occupied cells are in cells A1 to A10 |
| Column Reference | =COUNTA(C:C) | Count how many occupied cells are in column C |
| Row Reference | =COUNTA(1:1) | Count how many occupied cells are in row 1 |
| Multiple Columns/Rows | =COUNTA(A1:A10, C1:C10) | Count how many occupied cells are in A1 to A10 and from C1 to C10 |
| Cell and Range Names | =COUNTA(Sales_2012) | Count how many occupied cells are in the range named Sales_2012 * |
Count if cells A1 and B1 are occupied:-
Count how many occupied cells are in cells A1 to A10
Count how many occupied cells are in column A
Count how many cells are occupied in row 1
Count how many cells are occupied in A1:A10 and C1:C10
Ranges A1:A10 and C1:C10 contain namesand some cells are blank. To identify how many cells are occupied follow the below given steps:-
Conclusion:- Thus, the COUNTA function counts information, including error values but excludes empty cells.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"Hi Joe,
If your data set is in A1:A10, then the following formula will count the number of entries greater than 1 and less than 2:
=SUM(((A1:A10)>1)*((A1:A10)<2))
{If you see the characters < in the above formula, please replace with the 'less than' sign - it is an HTML rendering issue on the board, not you or me!}
Obviously you can replace 1 and 2 with any values you like.
HTH,
Alan"
how do use the countif formula when I only want to select certain cells and not an entire range? I keep getting a #value symbol.
How do I count the number of cells that contain a number that falls between two numbers. For example, How do I count the number of cells that falls between 24 and 48 hours?
This is the tip I was looking for years. Now, it becomes very easy to count characters. Great tip!!!
"If your data set is in A1:A10, then the following formula will count the number of entries greater than 1 and less than 2:
=SUM(((A1:A10)>1)*((A1:A10)<2))
{If you see the characters < in the above formula, please replace with the 'less than' sign - it is an HTML rendering issue on the board, not you or me!}
Obviously you can replace 1 and 2 with any values you like. "
how do use the countif formula when I only want to select certain cells and not an entire range? I keep getting a #value symbol.
How do I count the number of cells that contain a number that falls between two numbers. For example, How do I count the number of cells that falls between 24 and 48 hours?