Excel COUNTIF function Syntax with Examples

The Excel COUNT and COUNTA formula is useful for the basic counting requirements. However, sometime you need to count a number of cells in a range which meets givencriteria. To do this, COUNTIF formula is useful. So COUNTIF formula counts number of cells in a range based on the given criteria.
Syntax of Excel COUNTIF formula:
COUNTIF(range, criteria)
where
range is range of cells which you need to count
criteria is nothing but the condition on which the range should be calculated.

Example for Excel COUNTIF Formula:
Let us take below sample data and use countif function with different criteria.


=COUNTIF(C2:C7,50) :- Counts the range with Value 50
=COUNTIF(C2:C7,E1) : Counts the cells value equal to Range E1
=COUNTIF(C2:C7,">"&E1) : Counts the cells greater than value specified in the range E1
=COUNTIF(D2:D7,"<0 0="0" :=":" are="are" cells="cells" less="less" nbsp="nbsp" ounts="ounts" p="p" than="than" the="the" which="which">=COUNTIF(C2:C7,"*"): Counts the cells which contains text
=COUNTIF(A2:A7,"Apple"): Counts only the cells with exact word Apple; not case sensitive
=COUNTIF(A2:A7,"*Apple*"): Counts cells containing text Apple anywhere within the text
=COUNTIF(A2:A7,"*E") : Counts cells which cell content has the alphabet E in the last
=COUNTIF(B2:B6,">"&AVERAGE(B2:B6)) : counts the cells which are greater than average of the range.

Using COUNTIF function with named ranges: You can use Named Ranges in Excel countif function. For doing this first we need to define the range. Let us look into the aboveexample and define Range A1:A7 as " "Data".


Now we can use the names instead of writing the Ranges. The example for Countif function with named ranges.
=COUNTIF(Data,"Mango")
Similarly we can use defined names to any range by naming that range. It is useful when we use complicated countif functions.