In this lesson you can learn about COUNTIF function.
COUNTIF function counts the number of times within the specified range of cells that perform the specified value as a criterion.
Function syntax:
=COUNTIF (range, criteria)
- Range - the range of cells that you want to count the data. There has to be an ongoing area.
- Criteria - a condition that must be met for cell counting. In the case of numeric values - the condition can look like eg 100
Example 1 - count string with exact string of text
You want to count cells where there is only REPORT text. Cells are in C column.
The formula is =COUNTIF(C2:C10,”REPORT”)
Example 2 - greater than
You want to count cells where value is greater than 10. Cells are in C column.
The formula is =COUNTIF(C2:C10,">"&10)
Example 3 - greater than or equal to
You want to count cells where value is greater than or equal 10. Cells are in C column.
The formula is =COUNTIF(C2:C10,">="&10)
Example 4 - not equal to
You want to count cells where value is not equal to 10. Cells are in C column.
The formula is =COUNTIF(C2:C10,"<>"&10)
Example 5 - less than x but more than y
You want to count cells where value is less than 40 but more than 15. Cells are in C column.
The formula is =COUNTIF(C2:C10,"<"&40)-COUNTIF(C2:C10,"<="&15)
Example 6 - countif plus countif
You want to count cells where value is greater than 40 plus less than 15. Cells are in C column.
The formula is =COUNTIF(C2:C10,">"&40)+COUNTIF(C2:C10,"<"&15)
Example 7 - count cells containing string of text
You want to count cells which contain REPORT text. Cells are in C column. Use asterisk in your formula. Asterisk replaces any other signs.
The formula is =COUNTIF(C2:C10,"*REPORT*")
Example 8 - count cells beginning with string of text
You want to count cells which begin REPORT text. Cells are in C column. Use asterisk in your formula. Asterisk replaces any other signs.
The formula is =COUNTIF(C2:C10,"REPORT*")
Example 9 - count cells ending with string of text
You want to count cells which end RT text. Cells are in C column. Use asterisk in your formula. Asterisk replaces any other signs.
The formula is =COUNTIF(C2:C10,"*RT")
Example 10 - count cells ending with string of text and contains 6 letters
You want to count cells which end RT text and contains 6 letters. Cells are in C column. Use question mark in your formula. Question mark replaces one sign.
The formula is =COUNTIF(C2:C10,"????RT")
Example 11 - count cells containing any text
You want to count cells which contain any text. Cells are in C column. Use asterisk in your formula. Asterisk replaces any other signs.
The formula is =COUNTIF(C2:C10,"*")
Example 12 - count cells not containing any text
You want to count cells which not contain any text. Cells are in C column. Use asterisk in your formula. Asterisk replaces any other signs. <> means not equal to.
The formula is =COUNTIF(C2:C10,"<>"&"*")
Example 13 - greater than or equal to value from cell
You want to count cells where value is greater than or equal to value from cell C12. Cells are in C column.
The formula is =COUNTIF(C2:C10,">="&C12)
Example 14 - greater than value from cell
You want to count cells where value is greater than value from cell C12. Cells are in C column.
The formula is =COUNTIF(C2:C10,">"&C12)
Now you know how to use COUNTIF function in Excel. You know also many COUNIF formulas to use in your business cases.
Further reading: Countifs function COUNTIF for non-empty cells How to countif cells with partial match?