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)
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.
Similarly we can use defined names to any range by naming that range. It is useful when we use complicated countif functions.