COUNT, COUNTA, COUNTIFS in Excel

Excel is a powerful spreadsheet application that allows you to perform various calculations, analysis, and data manipulation tasks. One of the common tasks that you may encounter in Excel is counting the number of cells that meet certain criteria. For example, you may want to count how many products have been sold, how many students have passed an exam, or how many employees have a certain skill.

To help you with this task, Excel provides several functions that can count cells based on different conditions. In this article, we will explain the difference between three of these functions: COUNT, COUNTA, and COUNTIFS. We will also show you some examples of how to use them in your worksheets.

COUNT function

The COUNT function counts the number of cells in a range that contain numbers. It does not count cells that are empty, contain text, logical values (TRUE or FALSE), or errors. The syntax of the COUNT function is:

=COUNT(value1, [value2], ...)

The arguments value1, value2, etc. can be cell references, ranges, arrays, or constants. You can specify up to 255 arguments for the COUNT function.

For example, suppose you have the following data in cells A1:A10:

Data
10
20
apples
30
TRUE
40
#N/A
50
oranges
60

To count how many cells in this range contain numbers, you can use the formula:

=COUNT(A1:A10)

The result is 6, because only the cells A1, A2, A4, A6, A8, and A10 contain numbers.

COUNTA function

The COUNTA function counts the number of cells in a range that are not empty. It counts cells that contain any type of value, including numbers, text, logical values, errors, and empty strings (""). The syntax of the COUNTA function is:

=COUNTA(value1, [value2], ...)

The arguments value1, value2, etc. can be cell references, ranges, arrays, or constants. You can specify up to 255 arguments for the COUNTA function.

For example, using the same data as above, to count how many cells in the range A1:A10 are not empty, you can use the formula:

=COUNTA(A1:A10)

The result is 9, because only the cell A9 is empty. Note that the cell A5 contains a logical value (TRUE), the cell A7 contains an error (#N/A), and the cell A10 contains an empty string (""), but they are all counted by the COUNTA function.

COUNTIFS function

The COUNTIFS function counts the number of cells in a range that meet multiple criteria. You can specify up to 127 pairs of criteria ranges and criteria values for the COUNTIFS function. The syntax of the COUNTIFS function is:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

The arguments criteria_range1, criteria_range2, etc. are the ranges of cells that you want to apply the criteria to. The arguments criteria1, criteria2, etc. are the criteria that define which cells will be counted. The criteria can be numbers, expressions, cell references, or text values.

For example, suppose you have the following data in cells A1:B10:

Product	Sales
apples	100
oranges	200
bananas	150
grapes	50
pears	120
cherries	80
mangoes	90
kiwis	70
pineapples	110
strawberries	60

To count how many products have sales greater than 100, you can use the formula:

=COUNTIFS(B1:B10," >100 ")

The result is 4, because only apples, oranges, bananas, and pineapples have sales greater than 100.

To count how many products have sales between 50 and 100 (inclusive), you can use the formula:

=COUNTIFS(B1:B10," >=50 ",B1:B10," <=100 ")

The result is 5, because grapes, cherries, mangoes, kiwis, and strawberries have sales between 50 and 100.

To count how many products start with the letter “p”, you can use the formula:

=COUNTIFS(A1:A10," p* ")

The result is 3, because pears, pineapples, and strawberries start with the letter “p”. Note that the asterisk (*) is a wildcard character that matches any sequence of characters.

Summary

In summary,

The COUNT function counts the number of cells in a range that contain numbers.
The COUNTA function counts the number of cells in a range that are not empty.
The COUNTIFS function counts the number of cells in a range that meet multiple criteria.

You can use these functions to perform various counting tasks in Excel, depending on the type and condition of the data you have. For more information and examples, you can refer to the following sources:

COUNT, COUNTA, COUNTIFS in Excel