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. With the Countif function’s help, we can count a fruit how many times it is getting repeated from the selected range or column.
What is COUNTIF Function?
COUNIF Function counts the cells that meet specific criteria or conditions. 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 of which cells to count. It can be a number, text string, cell reference, or expression.
COUNTIF Function comes under the 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 the Formulas tab.
- Select More Functions ribbon.
- Click on the Statistical option.
- Then it will open the drop-down list on the left side as per the below screenshot:
- Please select the 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 us understand the working of the COUNTIF Function in Excel by some COUNTIF Formula examples.
Example #1
We have the sales data of a company XYZ. We need to find out which salesperson sells the maximum and from which city we are ahead in profit to expand our business and distribute the bonus accordingly per the sales employee performance.
Now, if we summarize the above data, we can predict the below results:
4.8 (9,572 ratings)
View Course
Here we will use the COUNTIF Function.
- For the Count of sales, we will take a range of sales amounts.
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 can also highlight the data 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 the 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 a downloadable excel template. You can also go through our other suggested articles –
