Business Examples On How To Use COUNTIF Function In Excel

The COUNTIF function in Excel counts the number of cells that meet a single condition. The COUNTIF function in Excel is a powerful tool, especially in financial modeling, which gives you the ability to set a preferred condition before the cells are counted- it does not merely count just any number, text, value and blank cells. Learning how to use COUNTIF function Excel allows you to have different variations of the criteria.

The COUNTIF examples in Excel provided here will help you how to use Excel to COUNTIF greater than, use Excel to COUNTIF less than and many more that will give you insights to your business data.

The Nuts And Bolts on How To Use COUNTIF Function In Excel

The COUNTIF function in Excel is a built-in function categorized under Statistical Function. At its core, it is very similar to the COUNT function in Excel as it also counts the number of cells. But COUNTIF function in Excel gives you more flexibility by allowing you to be more specific on what to count.

To know the difference between the five ways to count in Excel, please feel free to read the article: How to Use Count Functions in Excel for Business.

The COUNTIF Formula in Excel

Where:

  • Range. The range of cells which will be assessed against your criteria. The criteria must be satisfied first before they will be counted. Otherwise, it will be ignored.
  • Criteria. The defined condition or parameter where each of the cells in a given range is assessed.

The formula or syntax for the COUNTIF function in Excel is pretty straightforward but it lets you have numerous possible variations for your criteria such as:

  1. Numbers (5,100,.10.2.5)
  2. Dates and Time (10:00,29/01/2018)
  3. Text (“Mr Smith”,”January”)
  4. Logical Operators (<,>,<>,=)
  5. Wildcard characters (*,?)

A criterion using No. 1 to 4 in your COUNTIF function in Excel can be used as a Direct or Exact Match with the single criteria whereas using No.5 can be used as a Partial Match.

As you can see, different variations and possibilities on how to use COUNTIF function Excel make it a powerful tool fit for many tasks. To illustrate, see the COUNTIF examples in Excel below using a hypothetical Men’s clothing store.

COUNTIF Example In Excel No. 1: How To Use COUNTIF Function Excel For Numbers & Text

This is one of the most basic uses of COUNTIF function in Excel. You can use the COUNTIF function in Excel to count the number of cells where a specific text is contained

For example, using a Sales Data list below we count the number of cells containing a sales person’s name.

The COUNTIF formula for Excel =COUNTIF(C3:C18,”John”) gives us a total count of 5. The Range is represented by cell range (C3:C18) and Criteria is the text (name) “John”.

It is important to keep in mind that a Text criterion is NOT case sensitive so if you type “john” or “JOHN”, this will not change the result.

How to use COUNTIF function in Excel for a Number criteria? It is basically the same. Assume we want to count the number of cells containing the number 3 under Sales Quantity (Column E).

The COUNTIF formula for Excel =COUNTIF(E3:E18,3) results in a total count of 3. The Range is represented by cell range (E3:E18) and Criteria is the number 3.

COUNTIF Example In Excel No. 2: How To Use COUNTIF Function Excel Using Wildcard Characters

In working with large data, it is unavoidable to have your Text criteria in several forms or variations.

Notice under Type of Shirt (Column B), a V-Neck shirt sold in the retail store has two versions – Classic and Basic. Assume, we want to count the number of cells that contain the text “V-Neck shirt” only and ignore the texts “Basic” and “Classic” in the Sales Data List. How to use COUNTIF function in Excel in this case?

Using the COUNTIF function in Excel, you can combine wildcard characters to filter the results.

Wildcard characters are special characters representing one or more other characters. The asterisk (*) is the most common and is used to match zero or more characters of characters.

  • Asterisk (*) – matches any one or more characters. This is used when counting cells containing a specific word or a character(s).
  • Question mark (?) – matches any single character. This is used when counting cells beginning and/or ending with certain characters.

The COUNTIF formula for Excel =COUNTIF(B3:B18,”*V-Neck*”) results in a total count of 4. The Range is represented by cell range (B3:B18) and Criteria is the “V-Neck”.

COUNTIF Example In Excel No. 3: How To Use COUNTIF Function Excel For Greater Than, Less Than, Or Equal To

COUNTIF function in Excel works also on counting the number of cells using logical operators (<,>,<>,=). Understanding how to use COUNTIF function Excel can help you count cells with values greater than, less than, or equal to, etc.

Based on the Sales List, you can analyze your sales performance based on Dollar Sales for the month similar to below.

As you can see using COUNTIF function in Excel can help analyze your business’ performance and compare it with key metrics to give you insights.

COUNTIF Example In Excel No. 4: How To Use COUNTIF Function Excel For Dates

If you have a similar list like our Sales Data example where Transaction Dates are important, you can use the COUNTIF function in Excel to count the number of cells that match a specific date.

You can also the COUNTIF function in Excel to count the number of cells that matches a range of specific dates, you can use the logical operators greater than or less than of a specific date.

COUNTIF Example In Excel No. 5: How To Use COUNTIF Function Excel With Cell Reference

Instead of typing the Text, Number, or Date criteria within the formula, you can use a reference cell. This will allow you to be more versatile, you can change your criteria without having to change the formula itself which will also help you avoid any errors.

How to use COUNTIF function Excel to count text and number using cell references? To illustrate, let’s incorporate this in our prior COUNTIF examples In Excel.

The COUNTIF formula for Excel =COUNTIF(C3:C18,B20) uses cell B20 as the reference cell. Whatever text or number is entered in Cell B20 will be the criterion.

How to use COUNTIF function Excel using wildcards with cell references? By simply linking the cell reference with your COUNTIF formula using a concatenation operator- the ampersand (&).

In our No.2 COUNTIF example In Excel, instead of typing “*V-Neck*” directly within the formula, you can type it in your reference cell, B20. Your new COUNTIF formula for Excel will be =COUNTIF(B3:B18, “*”&B20&”*”)

How to use COUNTIF function Excel if you want Excel to COUNTIF greater than or less than using a cell reference?

By enclosing the logical operators in quotes (“ “)  and adding an ampersand (&) before your selected reference cell (recall the symbol & will serve as linking operator).

Using COUNTIF example In Excel No. 3 and Cell B20 as your reference cell, the new COUNTIF formula for Excel will be =COUNTIF(F3:F18,”>”&B20) formula. This gives us a total count of 7.

Common Issues on How to use COUNTIF function Excel

If you followed the COUNTIF example in Excel and tried COUNTIF formula for Excel with your own business data but were not able to get it to work, take a look at some common issues. You might find the answer or helpful tips to solve these below.

  • COUNTIF function in Excel not working, #NAME? error.
    • Double-check the cell range, criteria, cell references, use of ampersand, double quotes and spaces. At times you may find the error was caused simply by a misspelling.
  • How to use COUNTIF function Excel with a non-contiguous (not adjoining) range of cells?
    • In our COUNTIF example in Excel No. 1, the formula =COUNTIF(E3:E18,3) uses E3:E18 cell range to count the cells containing number 3. But what if you only want specific individual cells to be counted, say E3, E10, E12, and E17? The result is a #VALUE! Error.
  • COUNTIF function in Excel does not work if the cell ranges are not adjoining. Nor does it allow you to specify several individual cells.
    • To fix this, you can use a combination of several COUNTIF functions in Excel.
  • How to use COUNTIF function Excel for color formatted cells?
    • Unfortunately, COUNTIF function in Excel does not allow using color formats as a criterion.
  • How to use COUNTIF function Excel properly with an ampersand (&) and quotes (“ ‘”)
    • This may be confusing and tricky at first but identifying what you are matching with your criteria is the key to understanding how to use COUNTIF function Excel with an ampersand (&) and quotes (“ ‘”)
      • If you use a number or a cell reference in the Direct or Exact match criteria, you do not need to use both ampersand and quotes.
      • If a criterion includes text, wildcard character or logical operator with a number, you must enclose it in quotes.
      • If a criterion is with a cell reference or another Excel function, you must use both ampersands to concatenate and quotes.
  • How to COUNTIF function Excel if you have more than one criteria? The COUNTIF function in Excel only works for one single criterion, if you need to use more use the COUNTIFS function in Excel.

Was this helpful?