How to use Excel COUNTIF Function

    how to use excel countif function

    Quick Intro

    Use Excel COUNTIF Function to count the number of cells which meet a given criteria. In simple words, you can specify a value & count the number of cells which have that value from a given range of cells.

    Syntax

    COUNTIF(range, criteria)

    range A range of cells from which you want to count the cells which meet criteria.

    criteria A criteria to check to count cells.

    More Information on Excel COUNTIF Function

    • You can use a number, text, cell reference or a formula as criteria.
    • You can also use wildcard characters in criteria.
    • Criteria other than numbers & formulas need to enclose in double quotation marks.
    • You can also use logical operators in criteria.
    • Criteria are case insensitive.

    Examples

    1. Using Logical Operators as a Criteria: In below example, I have used “>75” a logical operator to count the number of students who scored more than 75. If you want to use a logical operator you have to put it inside the double quotation marks.

    Syntax-For-Excel-COUNTIF-Function

    You can use (>, <, = or <>) operator in your criteria.

    2. Using Dates as a Criteria: In below example, I have used a date in criteria to find out how many employees have a week off on 4th July.

    Use Date Text Criteria In Excel COUNTIF Function

    I have inserted a date directly into the function as a text by using double quotation marks. COUNTIF Function will automatically convert a text into a date.

    Use Date Number Crieria In Excel COUNTIF Function

    In above example, I have inserted same date function as a number. As your know excel stores a date as a number. you can also insert a number which represents a date as per excel’s date system.

    Sample File

    download sample file to learn more about this tips

    What’s Next?

    To learn more about Excel COUNTIF Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

    There is also you have COUNTIFS function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.