Techbout

Technology Simplified.

  • Windows
  • Mac
  • iOS
  • Android
  • Gadgets

How to Use COUNTIF Function in Excel

By: Waseem Patwegar

The COUNTIF Function in Microsoft Excel is used to count cells matching a particular criteria or condition. You will find below a number of examples explaining the use of COUNTIF Function in Excel.

COUNTIF Function in Excel

COUNTIF Function in Excel

The COUNTIF Function in Excel can be used to count cells containing Numbers, Dates and text matching a specific criteria.

For example, if there is a list of people with Names and Ages, the COUNTIF function can be used to count number of people above the age of 18 or people having the same family name.

The COUNTIF Function supports logical operators (=, <, >) and wildcards (*,?) for counting cells matching partial criteria.

Syntax of COUNTIF Function

The Syntax of COUNTIF Function is COUNTIF (range, criteria)

  • Range: The range of cells to count
  • Criteria: The condition that defines the cells to be counted.

1. Use COUNTIF Function in Excel (Examples)

Now that you understand the Syntax of COUNTIF Function in Excel, we can take a look at some examples to understand the actual usage of COUNTIF Function.

Let us assume that you have an Excel worksheet with Names of people in Column A and their Ages in Column B.

The COUNTIF Function can be used to count people who are above 18 years, below 18 years and those who are exactly 18 years old.

1. To count number of people who are exactly 18 years of age, type =COUNTIF(B3:B11,18) and hit the enter key on the keyboard of your computer.

Excel COUNTIF Function to Count Values Equal to a Number

In above example B2 to B11 is the ‘Range’ of cells to Count and 18 (Age) is the Criteria.

Tip: Instead of typing B3:B11, it far more easier to select the range B3:B11 using the mouse.

2. To count people who are older than 18 years, type =COUNTIF(B3:B11,”>18″) and hit the enter key.

Excel COUNTIF Function to Count Values Greater Than a Number

Similarly, you can use the formula =COUNTIF(B3:B11,”<18″) to count people who are younger than 18 years of age.

3. To count people who have the name “Andy”, type =COUNTIF(A3:A11,”Andy”) and hit the enter key.

Excel COUNTIF Function to Count People With Same Name

2. Reference Cells in COUNTIF Function

The COUNTIF function allows you to reference other cells containing the values or the criteria.

For example, let us assume that the qualifying age for a certain activity is available in cell C3 as shown bwlow.

Excel COUNTIF Function Referring Values in Another Cell

In this case, you can count people who are older than 21 years by typing =COUNTIF(B3:B11,”>” & C3) and pressing the enter key.

3. Use Wildcard Characters in COUNTIF Function

As mentioned above, the COUNTIF Function supports the use of wildcard Characters (? & *).

The question mark (?) is used to match a single character, while the Asterisk (*) is used to match any sequence of characters.

In case you want to find a question mark (?) or an asterisk (*), you can use a tilde (~) in front of these Wildcard characters (~? or ~*).

4. Common COUNTIF Function Problems

Here are some of the problems that you may come across while using the COUNTIF Function in Excel.

1. COUNTIF Function counts both upper and lower case letters in text strings. For example, cells containing “Oranges” and “ORANGES” will be treated as the same.

2. The COUNTIF Function returns wrong or incorrect results when used to match strings greater than 255 characters.

For matching strings greater than 255 characters, you can make use of the “&” operator or the CONCATENATE Function.

For example, you can type =COUNTIF(A1:A10, “250 characters”&”remaining characters) to get over the limit of 255 characters.

3. COUNTIF Function reports an error (#Value!) when the formula containing the COUNTIF function refers to another worksheet that is closed. In other words, the referred worksheet needs to be open.

4. While counting text values, make sure the data does not contain leading spaces, trailing spaces, quotation marks or nonprinting characters.

Related
  • How to Use SUMIF Function in Excel
  • How to Use SUMIFS Function in Excel
  • How to Use VLOOKUP Function in Excel

Twitter
Facebook

Popular Now

  • How to Boot Windows 10 From USB Drive
  • How to Backup iPhone to iCloud
  • How to Check Hardware Specs in Windows 10
  • How to Remove Bloatware From Windows 10 PC
  • How to Setup Chromecast on Android and iPhone

Gadget Guide

  • Mac vs PC Comparison: Should You Buy Mac or PC
  • Chromebook vs Windows Laptop
  • Kindle E-Reader Comparison | Which Kindle to Buy
  • iPad vs iPad Pro Comparison
  • Advantages of Chromebooks

About | Privacy Policy | Disclosure | Terms | Contact
© Copyright 2019 @Techbout.com · All Rights Reserved