• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Techbout

Technology Simplified.

  • iOS
  • Windows
  • Mac
  • Internet

How to Use COUNTIF Function in Excel

By: Sam Patwegar | March 14, 2025

The COUNTIF Function in Microsoft Excel can be used to Count cells matching a particular criteria or condition. It can be used to Count cells containing Numbers, Dates and Text matching a specific criterion.

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.

COUNTIF Function in Excel

The Syntax of the COUNTIF Function in Microsoft Excel is COUNTIF (range, criteria).

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

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

Advertisement

Now that you understand the Syntax of COUNTIF Function, let us go ahead and take a look at some examples to understand the actual usage of COUNTIF Function.

1. Use COUNTIF Function in Excel (Examples)

Let us assume that you have an Excel worksheet with Names of people in Column A and their Ages in Column B and the given task is 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.

Sam Patwegar

10+ years experience in writing fluff-free troubleshooting guides with just the right and required amount of related information. Equally capable of In-depth technical writing.

Related
  • How to Hide And Unhide Sheets in Excel
  • How to Forward Outlook Mail to Another Email Address
  • How to Recover Permanently Deleted Emails in Outlook

Primary Sidebar

Advertisement

Recent Posts

  • Fix: System Tray Icon Missing in Windows 11/10
  • How to Use Internet Explorer in Windows 11
  • Fix: Cast to Device Not Working in Windows 11/10
  • Fix: MacBook Camera Not Working Issue
  • Multiple Ways to Disable Face ID on iPhone

Advertisement

© 2025 Techbout.com · All Rights Reserved
About | Privacy Policy | Terms | Contact
Twitter(X) | Facebook