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.

Know the difference between the five ways to count in Excel: 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, the question mark or asterisk,- 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, such as using quotation marks to count for text. 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 the 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 the COUNTIF 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). In summary, we can also say that a question mark matches any single character.
  • Question mark (?) – matches any single character. This is used when counting cells beginning and/or ending with certain characters. In summary, we can also say that an asterisk matches any sequence of 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.

Because we used these excel functions, we’re able to conveniently count how, let’s say, many times dollar sales reached greater than or equal zero to USD 105.00.

Therefore, we can say that 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 use the COUNTIF function in Excel to count the number of cells that match a range of specific dates, you can use the logical operators greater than or less than 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. For example, people usually use cell a1 as a reference cell where you put your searches for the COUNTIF function in Excel.

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 range of cells, 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 to count in Excel.
  • How to use COUNTIF function Excel for color formatted cells?
    • Unfortunately, the 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 use COUNTIF for the non-blank count of cells?? To use COUNTIF Excel for not blank cells, we need to use the COUNTA Function. COUNTA function in Excel counts all values, texts, logical values and errors. It leaves out blank cells.
  • How to COUNTIF function Excel? 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?