You can use the COUNTA function in Excel to count the number of cells in a range that are not empty.
Often you may want to use the COUNTA function with an IF function to count the number of cells that are not empty only if a cell in a corresponding column meets a certain requirement.
Fortunately you can just use the COUNTIFS function to accomplish this.
The following example shows exactly how to do so.
Example: Using COUNTA with IF in Excel
Suppose we have the following dataset that shows the points scored by various basketball players:
Suppose we would like to count the number of players with “Guard” in the Position column but only if the corresponding cell in the Points column is not empty.
We can type the following formula into cell D2 to calculate this number:
=COUNTIFS(A2:A16, "Guard", B2:B16, "<>"&"")
The following screenshot shows how to use this formula in practice:
The formula returns a value of 5.
This tells us that there are 5 players with a value of “Guard” in the Position column where the corresponding value in the Points column is not empty.
We can verify this is correct by manually identifying the five players that meet these criteria:
Each of the highlighted rows contains a player with a value of “Guard” in the Position column where the corresponding value in the Points column is not empty.
The following tutorials explain how to perform other common tasks in Excel: