Excel COUNT and COUNTA functions to count cells

Using COUNT function in Excel - things to remember

Below are the two simple rules by which the Excel COUNT function works.

1. If an argument(s) of an Excel Count formula is a cell reference or range, only numbers, dates and times are counted. Blanks cells and cells containing anything but a numeric value are ignored.
2. If you type values directly into the Excel COUNT arguments, the following values are counted: numbers, dates, times, Boolean values of TRUE and FALSE, and text representation of numbers (i.e. a number enclosed in quotation marks like "5").

For example, the following COUNT formula returns 4, because the following values are counted: 1, "2", 1/1/2016, and TRUE.

=COUNT(1, "apples", "2", 1/1/2016, TRUE)

Excel COUNT formula examples

And here are a few more examples of using the COUNT function in Excel on different values.

To count cells with numeric values in one range, use a simple count formula like

=COUNT(A2:A10)

The following screenshot demonstrates which types of data are counted and which are ignored:

To count several non-contiguous ranges, supply all of them to your Excel COUNT formula. For example, to count cells with numbers in columns B and D, you can use formula similar to this:

=COUNT(B2:B7, D2:D7)

Tips:

• If you want to count numbers that meet certain criteria, use either the COUNTIF or COUNTIFS function.
• If apart from numbers, you also want to count cells with text, logical values and errors, use the COUNTA function, which leads us right to the next section of this tutorial.

Count cells that meet one condition (COUNTIF)

The COUNTIF function is purposed for counting cells that meet a certain criterion. Its syntax requires 2 arguments, which are self-explanatory:

In the first argument, you define a range where you want to count cells. And in the second parameter, you specify a condition that should be met.

For example, to count how many cells in range A2:A15 are "Apples", you use the following COUNTIF formula:

=COUNTIF(A2:A15, "apples")

Instead if typing a criterion directly in the formula, you can input a cell reference as demonstrated in the following screenshot:

For more information, please see How to use COUNTIF in Excel.

Count cells that match several criteria (COUNTIFS)

The COUNTIFS function is similar to COUNTIF, but it allows specifying multiple ranges and multiple criteria. Its syntax is as follows:

The COUNTIFS function was introduced in Excel 2007 and is available in all later versions of Excel 2010 - 365.

For example, to count how many "Apples" (column A) have made $200 and more sales (column B), you use the following COUNTIFS formula:

=COUNTIFS(A2:A15,"apples", B2:B15,">=200")

To make your COUNTIFS formula more versatile, you can supply cell references as the criteria:

You will find plenty more formula examples here: Excel COUNTIFS function with multiple criteria.

Get a total of cells in a range

If you need to find out the total number of cells in a rectangular range, utilize the ROWS and COLUMNS functions, which return the number of rows and columns in an array, respectively:

=ROWS(range)*COLUMNS(range)

For example, to find out how many cells there are in a given range, say A1:D7, use the following formula:

=ROWS(A1:D7)*COLUMNS(A1:D7)