COUNTIF function in Excel - syntax and usage

6/29/2015

Excel COUNTIF examples ? not blank, greater than, duplicate or unique

HOME PRODUCTS

DOWNLOADS

SUPPORT

FORUMS BLOG ABOUT US

PURCHASE

COUNTIF in Excel - count if not blank, greater than, duplicate or unique

Jul

by Svetlana Cheusheva

Excel, Excel duplicates, Excel functions, Excel tips

223 Comments

2

Microsoft Excel provides several functions purposed for counting different kinds of cells, such as blanks or non-blanks, with number, date or text values, containing specific words or character, etc.

In this article, we will focus on the Excel COUNTIF function that is purposed for counting cells with the condition you specify. First, we will briefly cover the syntax and general usage, and then I provide a number of examples and warn about possible quirks when using this function with multiple criteria and specific types of cells.

In essence, COUNTIF formulas are identical in all Excel versions, so you can use the examples from this tutorial in Excel 2013, 2010, 2007 and 2003.

Excel COUNTIF function - syntax and usage Examples of how to use COUNTIF in Excel

COUNTIF formula for text and numbers (exact match) COUNTIF with wildcard characters (partial match) Count if blank or not blank Count if greater than, less than or equal to COUNTIF formulas for dates Excel COUNTIF with multiple criteria Count duplicates and unique values Excel COUNTIF - frequently asked questions and issues

COUNTIF function in Excel - syntax and usage

Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition.

For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify. Another typical use of COUNTIF in Excel is for counting cells with a specific word or starting with a particular letter(s).

The syntax of the COUNTIF function is very simple: COUNTIF(range, criteria)

As you see, there are only 2 arguments, both of which are required:

range - defines one or several cells to count. You put the range in a formula like you usually do in Excel, e.g. A1:A20. criteria - defines the condition that tells the function which cells to count. It can be a number, text string, cell reference or expression. For instance, you can use the criteria like these: "10", A2, ">=10", "some text". And here is the simplest example of Excel COUNTIF function. What you see in the image below is the list of the best tennis players for the last 14 years. The formula =COUNTIF(C2:C15,"Roger Federer")counts how many times Roger Federer's name is on the list:



Excel: featured articles

How to use Excel COUNTIFS and COUNTIF with multiple criteria

VLOOKUP in Excel ? tutorial with formula examples

Creating a drop down list: static, dynamic, from another workbook

Excel formulas for conditional formatting based on another cell value

COUNTIF in Excel - count if not blank, greater than, duplicate or unique

Using IF function: formulas for numbers, text, dates, blank cells

How to use Excel SUMIFS and SUMIF with multiple criteria

Change background color based on cell value

INDEX & MATCH functions in Excel - a better alternative to VLOOKUP

Outlook: featured articles

How to sync Google Calendar with Outlook

How to fix "Cannot start Microsoft Outlook" error

Merge duplicate contacts in Outlook

Remove duplicate emails in Outlook 2010 and 2013 quickly and safely

How to create and use e-mail templates in Outlook

Excel

Excel add-ins

Excel charts Excel consolidation

Excel duplicates

Excel formatting Excel fun

Excel functions

1/56

6/29/2015

Excel COUNTIF examples ? not blank, greater than, duplicate or unique Excel PivotTables

Excel randoms

Excel tips Excel Vlookup

Merge data in Excel Microsoft Office Microsoft Word new release Office 365 tips

Office fun

Office news

Office tips

Outlook

Outlook add-ins Outlook BCC

Outlook calendar Outlook duplicates

Outlook templates Outlook tips

PowerPoint Video

Note. A criterion is case insensitive, meaning that if you type "roger federer" as the criteria in the above formula, this will produce the same result.

Excel COUNTIF function examples

As you have just seen, the syntax of the COUNTIF function is very simple. However, it allows for many possible variations of the criteria, including wildcard characters, the values of other cells, and even other Excel functions. This diversity makes the COUNTIF function really powerful and fit for many tasks, as you will see in the examples that follow.

COUNTIF formula for text and numbers (exact match)

In fact, we discussed the COUNTIF function that counts text values matching a specified criterion exactly a moment ago. Let me remind you that formula for cells containing an exact string of text: =COUNTIF(C2:C15,"Roger Federer"). So, you enter:

A range as the first parameter; A comma as the delimiter; A word or several words enclosed in quotes as the criteria. Instead of typing text, you can use a reference to any cell containing that word or words and get absolutely the same results, e.g. =COUNTIF(C1:C9,C7).

Similar COUNTIF formulas work for numbers as well as for text values. As you can see in the screenshot below, the formula =COUNTIF(D2:D9,5)perfectly counts cells with quantity 5 in Column D.

Sign in

Register Log in

Ultimate Suite for Excel Professionals

COUNTIF formulas with wildcard characters (partial match)

In case your Excel data include several variations of the keyword(s) you want to count, then you can use a wildcard character to count all the cells containing a certain word, phrase or letters as part of the cell's contents.



2/56

6/29/2015

Excel COUNTIF examples ? not blank, greater than, duplicate or unique

Suppose, you have a list of tasks assigned to different persons, and you want to know the number of tasks assigned to Danny Brown. Because Danny's name is written in several different ways, we enter "*Brown*" as the search criteria =COUNTIF(D2:D10, "*Brown*").

An asterisk (*) is used to find cells with any sequence of leading and trailing characters, as illustrated in the above example. If you need to match any single character, enter a question mark (?) instead, as demonstrated below.

Count cells beginning or ending with certain characters

You can use either wildcard character, asterisk (*) or question mark (?), with the criterion depending on which exactly result you want to achieve.

If you want to know the number of cells that start or end with certain text no matter how many other characters a cell contains, use these formulas:

=COUNTIF(C2:C10,"Mr*")- count cells that begin with "Mr".

=COUNTIF(C2:C10,"*ed")- count cells that end with the letters "ed".

The image below demonstrates the second formula in action:

If you are looking for a count of cells that start or end with certain letters and contain the exact number of characters, you use the Excel COUNTIF function with the question mark character (?) in the criteria:

=COUNTIF(D2:D9,"??own")- counts the number of cells ending with the letters "own" and having exactly 5 characters in cells D2 through D9, including spaces.

=COUNTIF(D2:D9,"Mr??????")- counts the number of cells starting with the letters "Mr" and having exactly 8 characters in cells D2 through D9, including spaces.

Tip. To find the number of cells containing an actual question mark or asterisk, type a tilde (~) before the ? or * character in the formula. For example, =COUNTIF(D2:D9,"*~?*")will count all cells containing the question mark in the range D2:D9.

Excel COUNTIF for blank and non-blank cells

These formula examples demonstrate how you can use the COUNTIF function in Excel to count the number of empty or non-empty cells in a specified range.



3/56

6/29/2015

Excel COUNTIF examples ? not blank, greater than, duplicate or unique

COUNTIF not blank

In some of other Excel COUNTIF tutorials, you may come across formulas for counting nonblank cells in Excel similar to this one:

=COUNTIF(range,"*")

But the fact is, the above formula counts all cells that contain any text, meaning that cells with dates and numbers will be treated as blank cells!

If you need a universal COUNTIF formula for counting all non-blank cells in a specified range, here you go:

=COUNTIF(range,""&"")

This formula works correctly with all value types - text, dates and numbers - as you can see in the screenshot below.

COUNTIF blank

If you want the opposite, i.e. count blank cells in a certain range, you should adhere to the same approach - use a formula with a wildcard character for text values or another one (with the "" criteria) to count all empty cells.

Formula to count cells not containing any text: =COUNTIF(range,""&"*")

Translated into plain English, the criteria used in the above formula (""&"*") means to find cells not equal to *, i.e. not containing any text in the specified range.

Universal COUNTIF formula for blanks (all value types): =COUNTIF(range,"")

The above formula correctly handles numbers, dates and text values. For example, the formula =COUNTIF(C2:C11,"")returns the number of all empty cells in the range C2:C11.

Note. Please be aware that Microsoft Excel provides another function for counting blank cells =COUNTBLANK(range). For instance, the below formulas will produce exactly the same results as the COUNTIF formulas you see in the screenshot above:

Count blanks: =COUNTBLANK(C2:C11)

Count non-blanks: =ROWS(C2:C11)*COLUMNS(C2:C11)-COUNTBLANK(C2:C11)

Also, please keep in mind that both =COUNTIF(range,"")and =COUNTBLANK(range)count cells with formulas that only look empty. If you do not want to treat such cells as blanks, use this formula instead: =ROWS(C2:11)*COLUMNS(C2:11)-COUNTIF(C2:11,""&"").

For more information about counting blanks and not blanks in Excel, see the following tutorials: 3 ways to count empty cells in Excel and How to count non-empty cells in Excel.

COUNTIF greater than, less than or equal to

To count cells with values greater than, less than or equal to the number you specify, you simply add a corresponding operator to the criteria, as shown in the table below.

Please pay attention that in COUNTIF formulas, an operator with a number are always enclosed in quotes.



4/56

6/29/2015

Excel COUNTIF examples ? not blank, greater than, duplicate or unique

Criteria Count if greater than

Count if less than Count if equal to Count if not equal to

Count if greater than or equal to Count if less than or equal to

Formula Example

Description

=COUNTIF(A2:A10,">5") Count cells where value is greater than 5.

=COUNTIF(A2:A10,"=5") Count cells where value is greater than or equal to 5.

=COUNTIF(C2:C8,""&D3):

If you want to count cells that contain an actual operator as part of the cell's contents, i.e. the characters ">", "5*")will count all cells in the range D2:D9 with contents like this "Delivery >5 days" or ">5 available".

Using Excel COUNTIF function with dates

If you want to count cells with dates that are greater than, less than or equal to the date you specify or date in another cell, you proceed in the already familiar way using formulas similar to the ones we discussed a moment ago. All of the above formulas work for dates as well as for numbers. Let me give you just a few examples:

Criteria Count dates equal to the specified date.

Count dates greater than or equal to another date.

Count dates greater than or equal to a date in another cell, minus x days.

Formula Example

Description

=COUNTIF(B2:B10,"6/1/2014")

Counts the number of cells in the range B2:B10 with the date 1-Jun-2014.

=COUNTIF(B2:B10,">=6/1/2014")

Count the number of cells in the range B2:B10 with a date greater than or equal to 6/1/2014.

=COUNTIF(B2:B10,">="&B2-"7")

Count the number of cells in the range B2:B10 with a date greater than or equal to the date in B2 minus 7 days.

Apart from these common usages, you can utilize the COUNTIF function in conjunction with



5/56

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download