There are a number of COUNT functions in Excel that you can use to count your records. Here are the most used ones:
- COUNT – count the number of cells in a range that contain numbers
- COUNTA – counts the number of cells in a range that are not empty
- COUNTBLANK – counts the number of cells in a range that are empty
- COUNTIF – counts the number of cells in a range that meet a condition
- COUNTIFS – counts the number of cells in a range that meet a set of conditions
Let’s get into the details
COUNT – count the cells with numbers
Count is the most basic counting function. It counts all the cells that contain numbers into a specific range.
COUNT syntax
COUNT(value1, [value2],…) – where value1, value2 is a cell or range reference. Only the first parameter is mandatory, the rest (up to 255) are optional
COUNT example
In the example below, we have a range of cells from A1 to C3 containing mixed values (text and numbers). The COUNT function in E2 will count the numbers in the range and return the value 3. There are 3 numeric values in the provided range.
COUNTA – counts non-empty cells
The COUNTA function can be used to count all the cells that have something in them. You can use the function to determine if a large data-set have any empty cells
COUNTA syntax
COUNTA(value1, [value2],…) – where value1, value2 is a cell or range reference. Only the first parameter is mandatory, the rest (up to 255) are optional
COUNTA example
In the example below, we have a range of cells from A1 to D4 containing values and blanks. All the cells marked in orange are blanks. All the cells marked in blue contain some values (text or numbers). The COUNTA function in E2 will count all the cells containing something in the provided range. There are 9 cells containing values so the function COUNTA returns the value 9 ignoring all the orange cells.
COUNTBLANK – counting the blank cells
The COUNTBLANK function is the opposite of COUNTA function. This function will count all the cells in the range containing blanks (nothing).
COUNTBLANK syntax
COUNTBLANK(range) – where range is a range reference. The function will count all the blanks in the specified range.
COUNTBLANK example
In the example below, we have a range of cells from A1 to D4 containing values and blanks. All the cells marked in orange are blanks. All the cells marked in blue contain some values (text or numbers). The COUNTBLANK function in E2 will count all the cells containing nothing (blanks) in the provided range. There are 7 cells containing nothing so the function COUNTBLANK returns the value 7 ignoring all the blue cells.
COUNTIF, COUNTIFS – count based on a condition or multiple conditions
This function is useful if you want to count records in a range that satisfy a certain condition(COUNTIF) or multiple conditions (COUNTIFS).
COUNTIF / COUNTIFS syntax
- COUNTIF(range, condition)
- COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
- COUNTIF(Where do you want to look?, What do you want to look for?)
COUNTIF / COUNTIFS example
Following the examples above, let’s assume that our range contains numbers between 1 and 16. and we want to count how many number are less than 9. As you can see below, there are 8 numbers less than 9 so the result of the function is 8.
If we want to use multiple conditions to determine how many numbers are less than 9 but greater than 4 we would us COUNTIFS as in the example below. The result would be 4.
Of course with COUNTIFS, the ranges do not have to be identical. You can use different ranges. For example, if you have two identical ranges with a different dataset like the ones below, you can use COUNTIFS to find out how many teams with more than 9 team members got more than 5 medals. The first range/condition set will identify 4 teams with more than 9 members wile the second will reduce the count to 2 because only 2 of those teams have more than 5 members. Notice that the ranges are identical in size (number of columns/rows). This formula only works if you are evaluating identical sized ranges.
