Excel Function Translator Logo
    The Excel Translator

    COUNTIF

    Statistical

    Counts the number of cells within a range that meet a single condition. Great for counting specific values or matching criteria.

    Translations
    EnglishCOUNTIF
    FrenchNB.SI
    SpanishCONTAR.SI
    GermanZÄHLENWENN
    ItalianCONTA.SE
    PortugueseCONT.SE
    DutchAANTAL.ALS
    PolishLICZ.JEŻELI
    RussianСЧЁТЕСЛИ
    TurkishEĞERSAY
    CzechCOUNTIF
    HungarianDARABTELI
    SwedishANTAL.OM
    DanishTÆLHVIS
    FinnishLASKE.JOS
    Syntax
    COUNTIF(range, criteria)

    Arguments

    • rangeThe range of cells to count
    • criteriaThe condition that determines which cells to count
    Examples
    =COUNTIF(A1:A10,">5")
    4

    Counts cells with values greater than 5

    =COUNTIF(B1:B10,"Yes")
    6

    Counts cells containing the text 'Yes'

    =COUNTIF(A:A,A1)
    3

    Counts how many times the value in A1 appears in column A

    Tips & Best Practices
    • •Use wildcards: * for any characters, ? for single character
    • •For multiple conditions, use COUNTIFS instead
    • •Criteria is not case-sensitive for text
    Common Mistakes
    • •Skipping the quotes around text or operators - COUNTIF(A:A,Yes) fails, COUNTIF(A:A,"Yes") works
    • •Trying to sneak two conditions into one COUNTIF. It only takes a single range/criteria pair, no matter how you phrase it
    • •Getting 0 back and assuming the formula is broken, when really the numbers you're counting are stored as text
    Related Functions
    COUNTIFSReach for this the moment you need two or more conditions true at once
    SUMIFSame single-condition logic, but it adds up a range instead of counting it
    COUNTACounts every non-empty cell, no condition attached
    Frequently Asked Questions

    What is the difference between COUNTIF and COUNTIFS?

    COUNTIF takes exactly one range/criteria pair. COUNTIFS takes several, and only counts a row when every single one of them matches.

    Can COUNTIF count cells by their fill color?

    No, and this trips up a lot of people. COUNTIF only reads values, never formatting. Counting by color means a helper column or a bit of VBA.

    Why does COUNTIF return 0 even though I can see matching cells?

    Nine times out of ten it's one of three things: hidden leading/trailing spaces, numbers stored as text instead of real numbers, or an operator like ">=" typed outside the quotes.

    Need to translate a formula using COUNTIF?

    Use our translator to convert your complete formula