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 given criteria. 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,”=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 above example 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.0>