# COUNTIF function

Applies to: Excel for Office 365, Excel for Office 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

With COUNTIF, one of the statistical functions, you can count the amount of cells which qualify a criterion; for instance, to count how often a specific city recurs in a customer list.

In its lowest form, COUNTIF says:

• =COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

• =COUNTIF(A2:A5,”London”)
• =COUNTIF(A2:A5,A4)

## Syntax

COUNTIF(range, criteria)

## Examples

To practise with such examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

## Best practices

Note: The COUNTIF function will not count cells based on cell background or font colour. However, Excel supports User-Defined Functions (UDFs) using the Microsoft Visual Basic for Applications (VBA) operations on cells based on background or font colour. Here is an example of how you can Count the number of cells with specific cell colour by using VBA.

## Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

To count cells that aren’t blank, use the COUNTA function

To count cells using multiple criteria, use the COUNTIFS function

See a video on how to use the COUNTIFS function

The SUMIF function adds only the values that meet a single criteria

The SUMIFS function adds only the values that meet multiple criteria

IFS function (Office 365, Excel 2016 and later)

Use the TRIM function to remove leading and trailing spaces from cells

Use the CLEAN function to remove non-printing characters from cells

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Statistical functions

Excel functions (alphabetical)

Excel functions (by Category)

Using SUMIF, COUNTIF, and related functions for quick data analysis (free preview)