Excel COUNTIF Formula (Table of Contents)
COUNTIF Formula in Excel
COUNTIF Formula in excel is an inbuilt or pre-built integrated function which is categorized under the statistical group of formulae.
Excel COUNTIF Formula counts the number of cells within a specified array or range based on a specific criterion or applied condition.
Below is the Syntax of COUNTIF Formula in Excel :
The COUNTIF Formula in Excel has two arguments i.e. range, criteria.
- Range: (Required & Compulsory argument) It is the range or array of cells on which the criteria should be applied, here array or range should be mentioned as e.g. C1:C12.
- Criteria: (Required & Compulsory argument)) It is a specific condition which will be applied to the cell values presented by the range of cells. It indicates the Count IF formula “what cells needs to be counted” here. There is a limitation of criteria argument in COUNTIF Formula, i.e. If the text string in criteria argument contains greater than 255 characters in length or more than 255 characters. Then it returns #VALUE! error.
Note: It can be either a cell reference or text string or logical or mathematical expression.
- A named range can also be used in COUNTIF formula.
- In the Criteria argument, Non-numeric values compulsorily should be always enclosed within double quotes.
- Criteria argument is case-insensitive, where it can consider any text case, it may be proper or upper or lower case.
- In Excel COUNTIF formula, below-mentioned arithmetic operators can also be used in criteria argument
= Equal to
< Less Than
<= Less than or equal to
> Greater Than
>= Greater than or equal to
<> Less than or greater than
How to Use COUNTIF Formula in Excel?
COUNTIF Formula in Excel is very simple and easy to use. Let’s understand the working of COUNTIF Formula in Excel with some examples.
Excel COUNTIF Formula – Example #1
In the following example, Table contains company employees name in column A (A2 to A15) & funds transferred status in column B (B2 to B15). Here I need to find out the count of two parameters i.e. funds transferred & funds not transferred in the dataset range (B2 to B15).
- Let’s apply COUNTIF function in cell D4. Select cell D4 where COUNTIF formula needs to be applied.
- Click or select an Insert Function (fx) button in the formula toolbar, a dialog box will appear, where you need to enter or type the keyword countif in the Search for a function box, COUNTIF formula will appear in select a function box. Double click on COUNTIF formula.
- A function argument dialog box appears where syntax or arguments for COUNTIF formula needs to be entered or filled. i.e. =COUNTIF (Range, Criteria)
- Range: It is a range of cells, where you want to count. Here the range or array is from B2 to B15, So select the column range. i.e. B2:B15
- Criteria: It is a condition or criteria where you insert the COUNTIF function, which cells need to be counted i.e. “Y” (here we need to find out the count of an employee who has received an amount or funds) Click OK, after entering both range & criteria argument. =COUNTIF (B2:B15,”Y”)
- COUNTIF formula returns the count of an employee who has received fund i.e. 8 from the dataset range (B2:B15). Here, COUNTIF formula returns a count. 8 within a defined range. It means eight number of employees have received funds.
- Suppose, if I want to find out the number or count of employee’s who have not received funds. Just I need to change the criteria argument, i.e. instead of “Y”, I need to use “N” =COUNTIF (B2:B15,”N”)
- COUNTIF formula returns the count of an employee who has not received a fund i.e. 6 from the dataset range (B2:B15). Here, COUNTIF formula returns a count 6 within a defined range. It means six number of employees who haven’t received funds.
Excel COUNTIF Formula – Example #2
With Arithmetic Operator in criteria argument
In the second example, Table contains students name in column A (A2 to A15), Total marks scored in column B (B2 to B15) and their percentage in column C (C2 to C15), Here I need to find out the count or number of students who have scored more than or equal to 90% in a dataset range (C2 to C15).
- Now, we can apply the COUNTIF formula with an arithmetic operator in the cell E3. i.e. =COUNTIF (Range, criteria)
- Range: It is a range of cells, where you want to count. Here the range or array is from C2 to C15, So select the column range. i.e. C2:C15.
- Criteria: It is a condition or criteria where you inform the count if function, which cells need to be counted i.e. “>=90%” (Here I need to find out the count or number of Students who has scored more than or equal to 90% in a dataset range (C2 to C15)).
- Here, =COUNTIF (C2:C15,”>=90%”) formula returns a value 3, i.e. No of Students who have scored more than or equal to 90% in a dataset range (C2 to C15).
Things to Remember About COUNTIF Formula in Excel
The Wildcard characters can also be used in excel COUNTIF formula (in criteria argument) to get the desired result, Wildcards helps out in partial matching.
The three most commonly & widely used wildcard characters in Excel COUNTIF formula are
- Asterisk (*): To match any sequence of either trailing or leading characters. E.g. Suppose if you want to match all cells in a range containing a text string beginning with letter “T” and ending with letter “e”, You can enter the condition or criteria argument as “T*e”.
- tilde (~): To find an actual question mark or asterisk. E.g. Suppose if you want to match all the cells in a range containing Asterisk (*) or Question mark (?) in the text, then tilde (~) is used in condition or criteria argument as “*~*shirt*” E.g. Red*shirt Green shirt
In the above example, it will track Red*shirt, because it contains Asterisk (*) character in between
Asterisk (*) usage examples
TOR* Finds the text containing or Starting with TOR
*TOR Finds the text containing or Ending with TOR
*TOR* Finds the text Containing the word TOR
- Question mark (?): Used to identify or track any single character. e.g. Suppose if you want to track “shirts” word or words ending with “S” in the below-mentioned example, then in criteria argument, I need to use “shirt?”, it will result in the value 1.
Red shirts
Green shirt
Recommended Articles
This has been a guide to COUNTIF Formula in excel. Here we discuss how to use COUNTIF Formula in excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
