COUNTIF Function in excel (Table of Contents)
COUNTIF Function in Excel
Countif function is the combination of Count and If function where count function counts the cells as per the criteria which have set in the Countif’s statement. For example, in a table we have some fruits name with the count. Now with the help of Countif function we can count a fruit how many time it is getting repeated from the selected range or column.
What is COUNTIF Function?
COUNIF Function counts the cells that meet specific criteria or condition. It can be used to count cells with text, numbers or dates that match specific criteria. The COUNTIF function also works with logical operators (<, >, <>, =) and wildcards (*, ?).
COUNTIF Formula in Excel
Below is the COUNTIF Formula in Excel :
The COUNTIF Function in Excel has two arguments i.e. range, criteria.
- Range: The range of cells to count. We give the range in a formula e.g. A1:A10.
- Criteria: This defines the condition that tells the function which cells to count. It can be a number, text string, cell reference, or expression.
COUNTIF Function comes under statistical function category and it’s a built-in function in excel. You can find this function under the ‘Formulas’ tab. Please follow the below steps:
- Click on Formulas tab.
- Select More Functions ribbon.
- Click on Statistical option.
- Then it will open the drop-down list in the left side as per below screenshot:
- Please select COUNTIF function from the drop-down list.
- It will open the Function Arguments pop-up as per the below screenshot:
- Under the Range field, enter a range of cells from which you want to count.
- Under the criteria field, enter the condition in the form of a text, expression or a number that specifies which cells to be counted.
How to Use COUNTIF Function in Excel?
COUNTIF Function in Excel is very simple and easy to use. Let understand the working of COUNTIF Function in Excel by some COUNTIF Formula example.
Example #1
We have the sales data of a company XYZ and we need to find out which salesperson sale the maximum and from which city, we are ahead in profit. So that we can expand our business and can distribute the bonus accordingly as per the sales employee performance.
Now if we summarize the above data, we can predict the below results:
4.8 (4,564 ratings)
View Course
Here we will use the COUNTIF Function.
- For Count of sales, we will take a range of sales amount.
The result will be :
- For sales are done by sales employee will take the range of sales employee field.
The result will be :
- For sales city wise will take the City field as the first parameter.
The result will be :
Apply the COUNTIF formula as per the required criteria and the result is:
Example #2
Highlight unique values in the below data
For calculating the duplicate/Unique values, we will use the below formula:
=COUNTIF(data, A1)=1
Explanation:
- Although Excel has many built-in options for highlighting data with conditional formatting. We also can highlight the data by using a formula.
- For highlighting cells that contain unique values in a set of data, we can use a formula that returns TRUE when a value occurs just once.
If you have values in the cells A16:D22 want to highlight cells with duplicate values, we will use below formula:
=COUNTIF($A$16:$D$22,A16)=1
How this formula works:
COUNTIF Function counts each value the number of times in the data set or range. According to this formula, each value must occur at least once in the data set, so when the count equals to 1, the value is unique.
Things to Remember
- In criteria, the wildcard characters? and * can also be used. A question mark matches any one character and an asterisk matches any sequence of characters.
- Before using COUNTIF Function in Excel, we need to be understood properly that Where do we want to look? and What do we want to look for?
- The second parameter ‘Criteria’ in the formula is case insensitive.
Recommended Articles
This has been a guide to COUNTIF Function in excel. Here we discuss the COUNTIF Formula and how to use COUNTIF Function in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
