How to Use COUNTIF Function in Excel, Conditional Counting

COUNTIF function in Excel is similar, however, COUNTIF is a conditional counting function. Please note, depending on the settings of your machine, the delimiter in functions could be either ',' or ';'. For the task requiring statistics with a specific condition, we use the COUNTIF function.

How to use COUNTIF function - Illustrated example

1. What is COUNTIF? Application in Excel

COUNTIF function is used for conditional data counting in Excel and is used with the syntax =COUNTIF(range;criteria).

Where:

+ Range: Mandatory part specifying the data range to count. It can contain numbers, arrays, or references containing numbers. + Criteria: Mandatory part specifying the condition to count values within the range. It can be a number, expression, cell reference, or text string.

2. Illustrated examples using COUNTIF function in Excel

To count the number of male students, we use the formula for cell D15 as follows: =COUNTIF(C5:C14,'Male')

2.1. Count occurrences in the table

Step 1: In the Excel data table, to count Nokia products, enter the formula: =COUNTIF(K7:K14,'Nokia'). Go to the cell where you want to display the result.

Step 2: Press Enter to display the result.

2.2. Count data based on a specific condition.

To count the number of sales over 100, use the command as follows: =COUNTIF(M7:M14,'>100') into the cell where you want to display the result.

- Press Enter to display the result.

2.3. Search and count occurrences of data.

To find the number of occurrences of iPhone data in the product category, use the following function: =COUNTIF(K7:K14,'i*') into the cell where you want to display the result.

Note: With i* indicating data with the first character being i.

- Then press Enter to display the result.

2.4. Using different characters for data counting.

In the Excel spreadsheet, enter the function =COUNTIF(K7:K14,'<>'&K9) where you want to display the result.

Note: The character <> is used to search for values not equal to (also known as not equal).

- Press Enter to display the result.

3. Compilation of errors when using COUNTIF function in Excel

Error #VALUE! is one of the most common errors when using the COUNTIF function as well as the COUNTIFS function.

3.1. Reference Formula to Closed Cells in the Worksheet

The COUNTIF/COUNTIFS function refers to cells within the data range of the closed worksheet and returns the error message #VALUE!.
Note: This error also occurs similarly with some other Excel functions such as SUMIF, SUMIFS, COUNTBLANK, and some other functions.
Error fixing solution: To resolve this error, simply open the worksheet containing the referenced data range in the formula, press the F9 key to refresh the formula.
You can also apply the above method to fix the #VALUE! error when using the SUM and IF functions in array formulas.

3.2 String Longer Than 255 Characters

Solution: Shorten the string if possible. If unable to shorten the string, use the CONCATENATE function or the (&) operator to split the value into multiple strings. For example:
=COUNTIF(B2:B12,'long string'&'another long string')

3.3 COUNTIF Function Doesn't Calculate Values on Discontinuous Data Range

Solution: Excel COUNTIF function does not work on discontinuous data ranges, and the syntax of the function allows specifying a separate cell as the first parameter. Instead, you can use a combination of multiple COUNTIF functions:
Incorrect formula: =COUNTIF(A2,B3,C4,'>0')
Correct formula: =COUNTIF(A2,'>0') + COUNTIF(B3,'>0') + COUNTIF(C4,'>0')
Alternatively, you can use the INDIRECT function to create array data. The following two formulas both return the same result:
=SUM(COUNTIF(INDIRECT({'C2:C8','E2:E8'}),'=0'))
=COUNTIF($C2:$C8,0) + COUNTIF($E2:$E8,0)

3.4. Using & And Double Quotation Marks (') in COUNTIF Formula

Solution: The syntax of the COUNTIF function is quite confusing. The & and double quotation marks (') are used in the formula to create text strings for the argument. You can adhere to the following rules:
- If using a number or cell reference in the exact comparison condition, you don't need the & and double quotation marks. For example: =COUNTIF(A1:A10,10) or =COUNTIF(A1:A10,C1).
- If your condition includes text, wildcard characters, or some operator along with a number, you must enclose it in double quotation marks. For example: = COUNTIF (A2: A10, 'orange') or = COUNTIF (A2: A10, '*') or = COUNTIF (A2: A10, '> 5')
- If using an expression with cell reference or other Excel function, you must use double quotation marks (') to start the text string and the & to concatenate and end the string.

Example:

- If unsure whether to add double quotation marks or the &, try adding these symbols.
In most cases, the & symbol will work. For example, the formulas:

all work and return the same result.

3.5. Error When Using COUNTIF Function to Count Cells with Colored Formatting

Solution: The Excel COUNTIF function syntax does not allow using format conditions as criteria. The only way to count or sum cells with colored formatting is to use a macro, or more precisely, use the Excel User-Defined function. Additionally, you can manually find active code in cells with colored formatting.

3.6. Excel COUNTIF Function Not Working

Solution: Suppose if you write the formula correctly but the COUNTIF function is not working, or returns incorrect results, try checking conditions such as range, criteria, cell reference, using the & symbol, and double quotation marks (').

Be cautious when using spaces in COUNTIF formulas. When creating calculation formulas, pay attention to spaces. If there is a space in the formula, it can cause errors. For example, the formula =COUNTIF(B2:B13,' orange').

At first glance, the formula seems correct, except for the space after the double quotation marks. Microsoft Excel will accept this formula without displaying any error messages, warnings, or other signs. Suppose in this case you want to count cells containing the word “orange”.

When using COUNTIF with multiple conditions, split the formula into different parts and check each function separately.

Similarly to the COUNTIF function, you can explore how to use the COUNTIFS Function, a conditional counting function. The above are just two of many specific examples in daily life where you might need to rely on and use the COUNTIF function to calculate the quantity of various issues in many specific tasks.

Count function is one of Excel's many functions, Similarly, if you're searching for a lookup function, Vlookup is the best choice for you. Vlookup function helps you find the best results with the fastest time.

In Excel, there are many useful shortcuts for users. 11 Excel shortcuts that Mytour recently shared will help you perform tasks with this tool faster, more professionally, in detail. You can view the list of 11 Excel shortcuts here.