COUNTIF in Excel – Shiksha Online

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
• Numeric Criteria
• Text Criteria
• Wildcards in COUNTIF
• Count Booleans
• COUNTIFS Function
• COUNTIFS Syntax
• Points to Remember

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.

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

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

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 –

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.

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.

---

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

---