COUNTIF function: Conditional counting in Excel

What is the COUNTIF function in Excel ? How to use the COUNTIF conditional counting function in Excel ? Let's find out together with TipsMake.com!

 

Microsoft Excel is a well-known spreadsheet software with many useful functions. Thanks to them, you can quickly calculate large amounts of data in sales reports, monthly payrolls, and much more. Of course, to use a particular function, you need to provide the corresponding data and conditions.

The COUNTIF function is a conditional counting function in Excel. You can use the COUNTIF function to count duplicate cells or count data. Below is more detailed information about COUNTIF, its syntax, and a few illustrative examples to help you understand how to use this basic Excel function .

What is the COUNTIF function used for?

COUNTIF is an Excel function used to count cells within a range that meet a single condition. COUNTIF can be used to count cells containing dates, numbers, and text. The criteria used in COUNTIF support logical operators (>, <, <>, =) and wildcards (*, ?) for partial matching.

COUNTIF is one of eight functions in Excel that divides logical criteria into two parts (range + condition). Therefore, the syntax used to build the criteria is different, and COUNTIF requires a range of cells, so you cannot use an array.

 

COUNTIF only supports a single condition. If you need multiple conditions, use the COUNTIFS function . If you need to manipulate values ​​in range arguments as part of a logic check, see the SUMPRODUCT and/or FILTER functions.

Syntax of the COUNTIF function in Excel

The syntax for the COUNTIF function in Excel is:=COUNTIF(range;criteria)

In this context, `range` is the area of ​​data you want to count and must contain it. It can contain numbers, arrays, or references containing numbers. Empty values ​​will be ignored. `criteria` is the required condition for counting the values ​​in the range and can be a number, expression, cell reference, or text string.

Note to readers:

1. The COUNTIF function returns a result after applying a condition to strings longer than 255 characters.
2. The criteria argument must be enclosed in quotation marks. Case-insensitive.

The question mark and asterisk characters can be used in criteria, where one question mark represents a single character and one asterisk represents a string of characters. Depending on your computer's settings, the separator in the function may be either comma or semicolon.

1. How to use the COUNT function in Excel
2. How to combine the Sumif and Vlookup functions in Excel
3. How to automatically display the name when entering a code in Excel

Examples of how to use the COUNTIF function.

We will learn how to use the COUNTIF function with the data table below and various data search examples.

 

1. Find the number of Mango items in the table.

The formula we need to use is =COUNTIF(B2:B8,"mango") , then press Enter to execute the function.

The result will be a value named "Mango" in the data table.

2. Find the number of items in the table that are not lemons.

We use the condition that the item name is not "Lemon" as '<>chanh' and then enter the formula =COUNTIF(B2:B8,"<>chanh") . The result will show 5 items that do not have the name "lemon" in the data table.

 

3. Find the number of items with a sales quantity of >= 10 kg.

The condition for use is '>=10' in the quantity sold column, using the formula =COUNTIF(C1:C8,">=10") and pressing Enter.

The result will show 6 items with sales quantities of >= 10 kg.

4. Search for orders named Chanh using alternative characters.

We can replace it with the * character to search for a value using the formula =COUNTIF(B2:B8,"C*") and press Enter.

The result will look like the image below.

 

5. Search for items with different names than in box B2.

Here, we will find items with names other than "Lemon" in cell B2 by using the '&' character before the cell reference, with the function syntax =COUNTIF(B2:B8,"<>"&B2) . The result will also show the exact number of items, which is 5.

Combine the COUNTIF function with other functions in Excel.

Use the RANK function in combination with the COUNTIF function.

The RANK.EQ function can be used in conjunction with the COUNTIF function to stop skipping numbers, but it will also ignore duplicate rankings.

To understand this better, let's look at how RANK.EQ works in conjunction with COUNTIF. The formula looks like this:

=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1

Implementing this formula will solve the problem of omitting numbers.

There is no overlap in the rankings above. However, James Walker and Gillian Tillman , who were supposedly ranked the same, are now ranked differently.

Therefore, using RANK.EQ with COUNTIF solved half the problem, but it didn't produce the desired result.

Use the SUMPRODUCT function with the COUNTIF function.

To rank students in a list by assigning the same rank to equivalent percentages without omitting any, you can use the SUMPRODUCT function with COUNTIF.

Please see the formula below:

The formula may seem complicated, but it's the best way to rank items accurately. This way, you can achieve the desired result, allow for rank overlap, and not overlook any numbers.

When providing results to your students, you can directly use the SUMPRODUCT formula as a replacement for the RANK function. To calculate non-duplicate rankings, you can use the RANK.EQ function alone or with the COUNTIF function.

Change the order of the final results.

On the Data tab , click the Sort and Filter group and select ascending order to sort by rank.

 

Compare the results in three adjacent rows to better understand how each ranking method works.

Common problems encountered when using the COUNTIF function in Excel

Problem	Solution
Returns false for long strings.	The COUNTIF function returns an incorrect result when you use it to match strings longer than 255 characters. To match strings longer than 255 characters, use the CONCATENATE function or the `&` operator &. For example: =COUNTIF(A2:A5,"long string"&"other long string").
No value is returned.	Make sure to enclose the criteria argument in quotation marks.
A COUNTIF formula receives a #VALUE! error when referencing a different worksheet.	This error occurs when a formula contains a function that references cells or ranges in a closed workbook or cells that have already been calculated. For this feature to work, you must open a different workbook.

The difference between the COUNTIF and COUNTIFS functions in Excel

Both COUNTIF and COUNTIFS serve the same purpose. Both are used to count the number of cells that match a given condition. COUNTIF is a simple function, suitable for simple checks. COUNTIFS, on the other hand, is extremely useful when you need to check data against multiple conditions. See more about using the COUNTIFS function .

It can replicate the functionality of COUNTIFS with many AND and OR functions in COUNTIF, but it's difficult to read and compose. COUNTIFS offers a simpler solution, allowing users to quickly analyze data with multiple conditions without nesting multiple levels of IF functions.

It's important to remember that COUNTIFS checks all conditions based on that data set. Therefore, if the data only matches one of the provided criteria, you should add more COUNTIF statements.

Overall, both COUNTIF and COUNTIFS are two excellent Excel functions for extracting necessary data from a large dataset. You just need to use them appropriately depending on the situation.

The above explains how to use the COUNTIF function with specific examples showing how to use the function and combine characters to find values ​​that meet a condition within a data range. Note that the use of separators ,may ;vary depending on your computer; if an error occurs due to the separator, you need to check the separator.

The COUNTIF function is typically used with statistical data tables, requiring the counting of cells containing values ​​that satisfy a given condition. The syntax of the COUNTIF function is quite simple; you only need to look at it once, or see the examples above from TipsMake.com, and you'll definitely know how to use it.

Using the COUNTIF function in Excel with multiple criteria

The COUNTIF function is not designed to count cells with multiple criteria. The COUNTIF function is used to count cells that match two or more criteria, such as logical AND. We can combine two or more COUNTIF functions in a formula to solve some simple tasks.

Count the difference between two numbers.

The most common application of the COUNTIF function in Excel, with two criteria, is to count numbers within a specific range, i.e., less than X but greater than Y.

Below is the formula used to count the cells in the range A2:A9 where the value is greater than 5 and less than 15.

=COUNTIF(A2:A9,">5") - COUNTIF(A2:A9,"=15)

Good luck with your project!

See more:

1. How to use the AVERAGEIF function in Excel
2. How to use the LEN function in Excel
3. How to use the Vlookup function in Excel

You've just finished reading the article "COUNTIF function: Conditional counting in Excel" edited by the TipsMake team. You can save countif-function-conditional-counting-in-excel.pdf to your computer here to read later or print it out. We hope this article has provided you with many useful tech tips and tricks. You can search for similar articles on tips and guides. Thank you for reading and for following us regularly.