How to use COUNTIF function on Excel

In Excel's basic functions, COUNTIF is the basic statistical function used to count cells that meet the given conditions within the specified range of conditions. At this time, the function syntax will have conditions attached. Often the COUNTIF function will be used with statistical data tables, which require certain values ​​to be searched for. The following article will guide you how to use the COUNTIF function on Excel to find the condition that meets the condition.

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

Instructions for using COUNTIF Excel function

The COUNTIF function on Excel has a syntax of = COUNTIF (range; criteria) .

In which range is the part of the area where you want to count the required data. May contain numbers, arrays or references containing numbers. Blank values ​​will be ignored. The criteria is a condition required to count values ​​in the range, be it numbers, expressions, cell references, or text strings.

Note to readers:

1. The COUNTIF function returns the result after using a condition with strings of characters greater than 255 characters.
2. The criteria need to be enclosed in quotation marks. Do not distinguish lowercase or uppercase letters.

You can use a question mark and an asterisk in a criteria condition, where a question mark is a character and an asterisk is a character string. Depending on the settings on the machine, the separator in the function is, or; to use.

We will learn how to use the COUNTIF function with the following data table and examples of finding different data.

How to use COUNTIF function on Excel Picture 1

1. Search for the number of product names Mango in the table

We have the implementation formula = = COUNTIF (B2: B8, "mango") and then press Enter to execute the function.

How to use COUNTIF function on Excel Picture 2

The result will output a name value Mango in the data table.

How to use COUNTIF function on Excel Picture 3

2. Find the number of non-Lemon items in the table

We use non-Lemon name condition '<> lemon' and enter the formula = COUNTIF (B2: B8, "<> lemon") . The result will be 5 items with no lemon name in the data sheet.

How to use COUNTIF function on Excel Picture 4

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

Terms of use with content are '> = 10' at the sales number column with the function formula = COUNTIF (C1: C8, "> = 10") and press Enter.

How to use COUNTIF function on Excel Picture 5

The result will be 6 items with the quantity sold> = 10 kg.

How to use COUNTIF function on Excel Picture 6

4. Search for an order named Lemon using alternate characters

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

How to use COUNTIF function on Excel Picture 7

The result will be as shown below.

How to use COUNTIF function on Excel Picture 8

5. Search for an item with a different name than B2

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

How to use COUNTIF function on Excel Picture 9

Here is how to use the COUNTIF function with specific examples to use the function and how to combine characters to find the value that satisfies the condition in the data area. Note with delimiters, or; depending on the machine, if you report an error in the mark you need to check the delimiter.

See more:

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

I wish you all success!