In this post, you’ll learn about COUNTIF Function, its syntax and the way of using COUNTIF Function in your excel spreadsheet.
What is COUNTIF Function?
COUNTIF Function in excel, counts cells that meet the single condition in a given range of data. Arguments can be dates, numbers and text. The criteria supports logical operators(>, <, <>, =) and wildcards (*, ?) for partial matching.
Syntax
=COUNTIF(range, criteria)
Arguments:
- = – built-in function.
- COUNTIF() – function name.
- range – the range of the given data.
- criteria – criteria that meets the given range.
Criteria can be also the value from another cell.
COUNTIF Function in excel is within a group of eight functions that splits logical criteria into two parts(range + criteria).
Note:
- COUNTIF Function is not a case-sensitive function. Use EXACT Function for case-sensitive counting.
- COUNTIF Function only supports with single condition. For Multiple conditions use COUNTIFS Function.
- Cell references are not enclosed with double quotes in criteria.
- COUNTIF Function requires a range; instead you can’t use an array.
- #VALUE! Error – when referencing other workbooks which are closed.
- To manipulate values in the range argument as part of a logical test, see SUMPRODUCT or Filter function.
How to use COUNTIF Function in Excel?
COUNTIF Function counts the cells that meet with a single condition.
DOUBLE QUOTES (“”):
- Text values are must be within double quotes, but numbers are not. If the logical operator with a number, the number and operator must be within double quotes.
- Value from another cell – value from another cell by using catenation.
- WILLCARDS:
The wildcards characters question mark (?), asterisk (*), or tilde (~) can used in criteria.The question mark (?) matches any single characters.
The asterisk (*) matches any sequences of characters.
The tilde (~) is an escape character it allows you to find the literal wildcard.
Not equal to – Use “<>” operator within double quotes to construct ” not equal to” criteria.
Blank cells – COUNTIF Function counts the cells that are blank and not blank.
Dates – the safest way of using dates is by Data Function or by referring valid date in another cell with cell reference.
Examples:
Step 1:
Open the workbook in your Microsoft Excel.
Step 2:
Enter the data, in the workbook.
STEP 3:
In the new cell, give the formula or the syntax. Start with ‘ =’ for every functions, COUNTIF to initiate functions name, followed by open parenthesis.
In this example, the range is sales values. Hence the values are from C3:C12.
STEP 4:
After calculating range, you should calculate criteria.
The criteria is with a condition >, refers to the count sales over 100.
STEP 5:
Press enter to get the results.
Return 5. The sales value above 100 is 5.
STEP 6:
Few examples;
- By using less than operator.
In this example range is from D3: D12 and the criteria are less than B1.
Note:
Less than operator is a text, and it is enclosed within double quotes.
Press enter to get the results.
- By using not equal to (<>) operator.
In this example, the range is D3:D12 and the criteria is not equal to.
Press enter to get the results.
Return 0. There is no match.
