COUNTIF in Excel – Shiksha Online

COUNTIF in Excel – Shiksha Online

6 mins read1.4K Views Comment
Rashmi Karan
Manager - Content
Updated on Jun 14, 2023 11:52 IST

Learn how to use the COUNTIF function in Excel using different criteria via different examples.


The COUNTIF function in Excel is a powerful tool that can quickly and easily count the number of cells in a range that meet a specific criterion. It is categorized under statistical functions. It counts the number of cells in a range that meet set criteria. You can use it as a worksheet function and enter it as part of a formula in a worksheet cell.



=COUNTIF(range, criteria)

  • Range (required): The range contains the values you want to count.
  • Criteria (required): The condition that determines the count of cells.
  • Criterion: The criteria for counting cells with text must be enclosed in quotation marks (“”). (Mandatory). To set criteria, signs such as “=”, “>”, “<” can be used. We can use wildcards to refer to the text.
Most Popular and Powerful Formulas in Excel
Most Popular and Powerful Formulas in Excel
The article discusses the most popular formulas in excel through practical examples of the use of formulas for Excel.
Mail Merge in Excel – Shiksha Online
Mail Merge in Excel – Shiksha Online
Mail Merge in Excel is widely used to simultaneously generate large amounts of documents while sticking to an agreed-upon or required format. Read the blog to learn more about more
What is Conditional Formatting in Excel?
What is Conditional Formatting in Excel?
Learn what is Conditional Formatting in Excel and explore different presets of Conditional Formatting to analyze and present your data in the most effective and powerful way.

Numeric Criteria

COUNTIF in Excel counts cells equal to a value, greater than or equal to a value, etc.

1. In the worksheet below, we will calculate – Sales over $10,000, Sales in the Furniture Department, and Sales in China.


Read MS Excel Tutorials

Here we will apply the COUNTIF formula in each calculation and the condition for which we need the result.

  • Sales over $10,000



The search range is Column D, which mentions the payouts and departments’ names. The formula counts the equal or greater values ​​in the range D2:D20 and tells us that there are 16 values ​​with that criterion.

Average Function in Excel – Formula and Examples
Average Function in Excel – Formula and Examples
The AVERAGE function is a popular Excel function used for various purposes, such as calculating average sales, average student scores, average monthly expenses, etc. It provides a quick and more
Top MS Excel Interview Questions
Top MS Excel Interview Questions
Master your Excel interview with confidence using our curated list of top questions. From basic functions to advanced data analysis, boost your Excel proficiency and shine in any data-driven role.
Introduction to MS Excel – A Beginner’s Guide
Introduction to MS Excel – A Beginner’s Guide
Let’s gets started with MS Excel, the most useful tool to crunch numbers and derive information for data analysts and scientists.

Text Criteria

Let’s use the COUNTIF function to check the text count according to the given criteria.

  • Determine Sales in the Furniture department

Here the search range is Column C which has the name of departments. The formula counts the equal or greater values ​​in the range D2:D20 and tells us there are 4 values ​​with that criterion.

  • Find out Sales Performance in China

Here is the search range in Column B with country names. The formula counts the equal or greater values ​​in the range D2:D20 and returns the number of values ​​with that criterion, which is 4. Notice that we haven’t used the title case for China in the formula.


The above image shows the final calculation for the given array.

Explore MS Excel Courses

Wildcards in COUNTIF

The COUNTIF function supports logical operators (>,<,<>,<=,>=) and wildcards (*,?) for partial matching. Wildcards are a set of characters that replace other characters in their place. Two wildcard characters can be used in COUNTIF in Excel:

  • Question mark (?): It replaces only one character individually. For example, if you use “S?” as a criterion for counting cells with text, all words that have a letter in place of the question mark will be counted, such as: “Yes”, “I know”, and “Your”.
  • Asterisk (*): It replaces a set of characters collectively. If now” S*” is used as the criteria to count cells with text, all words beginning with the letter “S” will be counted, such as: “Soup,” “Saturday,” and “Sound.”

Let’s understand the use of asterisk with an example –

In the below dataset, we intend to count the number of cells with the word “management.”


Step 1. We tried to calculate the count of cells with management using the general COUNTIF formula –


You can see that the count is 0.


Step 2. We will use wild card “*” in our COUNTIF formula to count cells with the given criteria.



Hence, using an asterisk helped us find cells with the text of interest.

Must Read – Most Useful Excel Formulas

COUNTIF in ExcelCount Booleans

You can use the COUNTIF function to count Boolean values – TRUE and FALSE 

1. In the below array, we have used the COUNTIF function to count the number of cells that contain the value TRUE.



2. Here, we have used the COUNTIF function to count the number of cells that contain the value FALSE.



COUNTIFS allows counting fulfilling more than one criterion. The COUNTIF function applies when there is a single criterion or a single condition, but if two or more conditions are required, we will need the COUNTIF SET function.


=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

We have the following table of TL Name, Departments, and Team Members under TL. We want to list several team members for a given department. To achieve this, we will – 

  • Mention the range for the type B2:B17 (the Type 1 values), then add a comma
  • Include the criteria (the cell E3, which has the value “Department”), add a comma
  • Specify the range for the second condition C2:C17 (the Team Members under TL values), add a comma
  • Mention the criteria (the cell F3, which has the value “1”)

So our formula is –


Now we have to change the criteria in every cell, so we have the below formulae for the respective teams –

Product Management =COUNTIFS(B2:B17,E3,C2:C17,F3)
Legal =COUNTIFS(B2:B17,E4,C2:C17,F4)
Engineering =COUNTIFS(B2:B17,E5,C2:C17,F5)
Business Management =COUNTIFS(B2:B17,E6,C2:C17,F6)

You may like – MS Excel interview questions


COUNTIF in Excel – Points to Remember

  • COUNTIF requires a range.
  • It supports only one condition. To apply multiple criteria, use the COUNTIFS function.
  • Text strings should be enclosed in double quotes (“”).
  • Wildcard characters “?” and “*” can be used in the criteria.  
  • COUNTIF supports strings longer than 255 characters.
  • COUNTIF returns a #VALUE error when referencing any close workbook.
  • The COUNTIF function does not count blank cells.  
  • COUNTIF is not case-sensitive.
  • Wildcard characters cannot replace numbers to be counted.


We hope this article helped you to understand COUNTIF in Excel. You can define any criteria according to your needs, and Excel will do the counting only if that criterion is met. In other words, it counts only the values ​​that meet the criteria.

Read More in Excel

MIS Reports in Excel | Excel Interview Questions | Basic Excel Formulas | Difference Between Formula and Function in Excel | Average Function in Excel | Introduction to MS Excel | HLOOKUP in Excel | ROW and COLUMN in Excel | Financial Modelling in Excel | Percentage In Excel | Remove Duplicates In Excel | Merge Cells in Excel | MIN and MAX Functions in Excel | Combine Text Strings in Excel | Import Data from PDF to Excel | Pivot Table In Excel | How To Enable Macros In Excel | How to Import Text Files to Excel | TRIM Function in Excel


How do I use Countif in Excel?

To use the COUNTIF Function in Excel, you would need to perform the following steps - Select a cell; Type =COUNTIF; Double click the COUNTIF command; Select a range; Type , (Comma); Select a cell (enter the criteria); Press enter.

How do I use COUNTIF in Excel for multiple columns?

To use COUNTIF in Excel for multiple columns, perform the following steps - Step 1: Enter the criteria you want to test for; Step 2: Type u201c=COUNTIFS(u201c and select the range you want to test the first criteria on; Step 3: Enter the test for the criteria; Step 4: Select the second range you want to test.

What is the difference between COUNTIF and COUNTIFS?

The primary difference between COUNTIF and COUNTIFS functions is that COUNTIF counts cells with a single condition in one range. At the same time, COUNTIFS is designed to evaluate different criteria in the same or different ranges.

What is COUNTIF function in Excel?

The COUNTIF function is one of the statistical functions in Excel that allows us to count the number of cells that meet established criteria. For example, how to count the number of times a product or service appears in a range of cells in a list or relationship of sales.

Download this article as PDF to read offline

Download as PDF
About the Author
Rashmi Karan
Manager - Content

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio


We use cookies to improve your experience. By continuing to browse the site, you agree to our Privacy Policy and Cookie Policy.