MS Excel: COUNTIF Function (WS)

In Excel, theCOUNTIFfunction counts the number of cells in a range, that meets a given criteria.

The syntax for theCOUNTIFfunction is:

rangeis the range of cells that you want to count based on thecriteria.

criteriais used to determine which cells to count.

Applies To:

Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Type of Function:

Worksheet function (WS)

Worksheet Function Example:

Lets take a look at an example to see how you would use theCOUNTIFfunction in a worksheet:

Based on the Excel spreadsheet above, theCOUNTIFfunction would return the following:

=COUNTIF(A2:A7, D2)would return 1=COUNTIF(A:A, D2)would return 1=COUNTIF(A2:A7, ">=2001")would return 4

Using Named Ranges

You can also use a named range in theCOUNTIFfunction. For example, weve created a named range calledfamilythat refers tocolumn Ain Sheet 1.

Then weve entered the following data in Excel:

Based on the Excel spreadsheet above:

=COUNTIF(family, D2)would return 1=COUNTIF(family, ">=2001")would return 4

To view named ranges: Under the Insert menu, select Name > Define.

Frequently Asked Questions

Question:Im trying to use COUNTIF on a selection of cells (not necessarily one solid range), and the syntax of the function does not allow that. Is there another way to do this?

Heres an example of what Id like to be able to do:

=COUNTIF(A2,A5,F6,G9,">0")

Answer:Unfortunately, the COUNTIF function does not support multiple ranges. However, you could try summing multiple COUNTIFs.

For example:

=SUM(COUNTIF(A2,">0"),COUNTIF(A5,">0"),COUNTIF(F6,">0"),COUNTIF(G9,">0"))

OR

=COUNTIF(A2,">0")+COUNTIF(A5,">0")+COUNTIF(F6,">0")+COUNTIF(G9,">0")

Question:I am using the COUNTIF function and I would like to make the criteria equal to a cell.

For example:

=COUNTIF(C4:C19,">=2/26/04")

I want to replace 2/26/04 with cell A1. How do I do this?

Answer:To use a cell reference in the criteria, you could do the following:

=COUNTIF(C4:C19,">="&A1)

=COUNTIF(A2:A7, D2)would return 1=COUNTIF(A:A, D2)would return 1=COUNTIF(A2:A7, ">=2001")would return 4=COUNTIF(family, D2)would return 1=COUNTIF(family, ">=2001")would return 4

=COUNTIF(A2,A5,F6,G9,">0")

=SUM(COUNTIF(A2,">0"),COUNTIF(A5,">0"),COUNTIF(F6,">0"),COUNTIF(G9,">0"))

=COUNTIF(A2,">0")+COUNTIF(A5,">0")+COUNTIF(F6,">0")+COUNTIF(G9,">0")

=COUNTIF(C4:C19,">=2/26/04")

=COUNTIF(C4:C19,">="&A1)