COUNTIF in Excel – Shiksha Online

# COUNTIF in Excel – Shiksha Online

clickHere
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.

Content

## COUNTIF Syntax

#### =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
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 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 mail...read more 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.

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

• Sales over \$10,000

=COUNTIF(D2:D20,”>10000″)

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
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 efficient...read more 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
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 –

=COUNTIF(B2:B17,”management”)

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.

=COUNTIF(B2:B17,”*management”)

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

Must Read – Most Useful Excel Formulas

## COUNTIF in Excel – Count 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.

=COUNTIF(C2:C17, “TRUE”)

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

## COUNTIFS Function

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 Syntax

### =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 –

=COUNTIFS(B2:B17,E3,C2:C17,F3)

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

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.

## Conclusion

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.

## FAQs

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.

clickHere

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

## Trending Management Courses Excel for Beginners
Great Learning 4.6 Microsoft Excel - Excel from Beginner to Advanced
UDEMY 4.7 Introduction to MS Excel
Simplilearn 4.6  Create Charts and Dashboards Using Microsoft Excel
Coursera 4.0Starts today Using Basic Formulas and Functions in Microsoft ExcelUsing Basic Formulas and Functions in Microsoft Ex...
Coursera 5.0Starts today

## Top Picks & New Arrivals        