What is the COUNTIF function in Excel? Syntax and specific examples.

While working with Excel, many users often need to count the number of values ​​within a specific range. To solve this problem, the COUNTIF function in Excel was created and has become a useful tool for data analysis. So what is the COUNTIF function, and what is its syntax? Let's find out in detail in the article below:

What is the COUNTIF function? COUNTIF function syntax

The COUNTIF function in Excel is used to count cells within a range that meet ONE condition.

You can use the COUNTIF function to count duplicate cells or count data. This conditional counting function is often used with statistical data tables, requiring you to count the number of cells containing values ​​that satisfy a given condition.

The syntax for the COUNTIF function in Excel is as follows:

 

=COUNTIF(range, criteria)

In there:

1. range: Data range (Example: You need to count cells from A1:A100)
2. criteria: Condition (Example: Count cells with values ​​>5)

Notes on using the COUNTIF function:

1. If the condition is text (e.g., "Pass") or a comparison symbol (e.g., >5), it must be enclosed in quotation marks ("").
2. The function is not case-sensitive (for example, "Đạt" or "đạt" will both be counted).
3. COUNTIF only counts ONE condition; if you want to count MULTIPLE conditions, use COUNTIFS.

When should you use the COUNTIF function in Excel?

COUNTIF is a function in Excel used to count the number of times a certain condition appears within a specific range or interval, combining the COUNT and IF functions in Excel for flexibility.

You should use the COUNTIF function when you want to:

1. Count the number of occurrences of a specific value: For example, count the number of students who scored above 8 in a list of grades.
2. Counting based on text conditions: For example, counting the number of times the word 'Pass' appears in a list of results.
3. Counting the number of times a certain number appears: Counting the number of times a specific number, such as 10, appears within a range.
4. Counting based on arithmetic conditions: You can use conditions like '10', '<=20', or '<5' to count values ​​based on arithmetic conditions.

1. Counting based on dates: For example, count the number of days in a list that have a date greater than January 1, 2020.
2. Condition-based counting: For example, you can use this function in combination with other functions like the AND function in Excel or OR (by using additional functions like COUNTIFS) to count based on multiple conditions at once.
3. Identify the occurrence of a pattern: For example, you want to know how many lines in an email list contain '@domain.com'.

In general, whenever you want to know the number of items in a range or data set that satisfy a certain condition, COUNTIF is the ideal tool to use.

How to use the COUNTIF function in Excel, with specific examples.

Let's explore how to use the COUNTIF function through various data search examples.

Example 1: Use the COUNTIF function to find the number of Watermelon items in the following table.

The formula is as follows: =COUNTIF(B2:B8,'watermelon') then press Enter to execute the function.

And the result will count out 2 values ​​named Watermelon.

Example 2: Using the same table, we will count the number of items weighing more than 6 kg.

The formula is as follows: =COUNTIF(C2:C8,'6') then press Enter to execute the function.

The result is that there are 3 items with a weight of 6kg.

In addition, this applies to the following cases:

1. To count quantities less than 6kg, change the criteria argument to '<6'.
2. To count quantities greater than or equal to 6kg, change the criteria argument to '=6'.
3. To count quantities less than or equal to 6kg, change the criteria argument to '<=6'.

Example 3: Use the COUNTIF function to find the number of rows that are not "Watermelon" in the table.

The formula is as follows: =COUNTIF(B2:B8,'<')  and then press Enter to execute the function. Where '<' is a different character.
 

The count revealed that there were 5 items that were not watermelons.

Example 4: Use the COUNTIF function to search for orders with the name "Durian" using replacement characters.

To count items using wildcards, in the criteria argument, enter the first letter of the item name followed by an uppercase *.

*Note: This method is only applicable when the items have different initial letters.

The formula is as follows: =COUNTIF(B2:B8,'S*')  then press Enter to execute the function.

And here are the results.

Example 5: Search for items with names different from cell B3

Here we will find items with a name other than Mango in cell B3 by using the characters '<' and '&' before the cell reference, with the function syntax =COUNTIF(B2:B8,'<'&B3) .

The result will show the exact number of items as 6.

Can the COUNTIF function be combined with other mathematical operations?

Although the COUNTIF function can perform basic calculations, you can combine it with other functions and operations in Excel to perform more complex tasks.

For example, you might want to count the number of cells that meet different conditions within different ranges, and you can absolutely do this by combining the COUNTIF function with addition, subtraction, multiplication, division, or even other functions.

For example, suppose you want to count the number of cells with values ​​greater than 10 in the range A1:A10 and simultaneously count the number of cells with values ​​less than 5 in the range B1:B10. You can use the following formula:

 =COUNTIF(A1:A10, ">10") + COUNTIF(B1:B10, "<5")

This formula will return the total number of cells that satisfy both conditions. The COUNTIF function allows you to easily combine these conditions without having to use more complex calculation tools. This is one of the reasons why COUNTIF is an extremely useful and easy-to-use function in Excel.

Is it possible to use the COUNTIF function to count unique values?

Although the COUNTIF function is powerful and versatile in counting the number of cells that meet a specific condition, it cannot directly count unique values ​​within a data range. This means that if you want to know the number of unique values ​​that appear in a range, the COUNTIF function cannot do so directly.

However, you can combine the COUNTIF function with other tools in Excel to achieve this goal. One common way is to use the UNIQUE function in Excel 365. The UNIQUE function can help you extract unique values ​​from a range, and then you can use COUNTIF to count those values.

For example, to count the number of times the value in cell A1 appears in the range A1:A10, you can use the following simple formula: =COUNTIF(A1:A10, A1)

This helps you count the number of times a specific value appears within a range. If you want to count all unique values ​​in the range, you can use a formula with the UNIQUE function to filter out non-duplicate values, and then use COUNTIF to count them. However, this requires an additional data filtering step or the use of other combined functions.

The above article guides you on how to use the COUNTIF function - a function for counting quantities with conditions in Excel. Hopefully, this information is helpful to you. You can also find more Excel tips and tricks at [link/website].

Frequently Asked Questions

How do I count the cells that contain a portion of text (relative counting)?

To count the cells containing a specific word within a sentence, you need to use the asterisk (*). The asterisk represents any string of characters. When used, the COUNTIF function will count all cells containing the "keyword" at any position (beginning, middle, or end of the cell). For example, =COUNTIF(data_range, "*Hanoi*")

Why does the COUNTIF function return 0 even though there is data?

The COUNTIF function returning 0 may be due to extra spaces in the data, or a discrepancy between Text and Number formats. Additionally, please check the formula separator (, or ;) as set by your computer.

Is the COUNTIF function case-sensitive?

No. The COUNTIF function by default treats "EXCEL" and "excel" as the same thing.

You've just finished reading the article "What is the COUNTIF function in Excel? Syntax and specific examples." edited by the TipsMake team. You can save what-is-the-countif-function-in-excel-syntax-and-specific-examples.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.