How to use COUNTIF function in Excel?

COUNTIF Function

COUNTIF is Microsoft Excel Function, its count the range of cells in excel with criteria.

Inbuilt Function

COUNTIF function is an inbuilt function in Microsoft Excel which is categorized as Statistical Function.

Operators & Wildcards

We can use operator (<, >, =, <>,>=, <=) and Wildcards (*, ?) for COUNTIF function. Operators are used for logical operations and wildcards are used for the partial match.

How to use Countif Function in Excel

countif formula in excel

countif example in excel sheet

Syntax – There are two arguments in COUNTIF Function.

                =COUNTIF(range,criteria)

(A). Range – The Range of cells that we apply criteria against.

(B). Criteria – We Count based on criteria value in cells. We use criteria from two technique.

(I). ByVal – =COUNTIF($D$2:$D$9428,”Varanasi“), this is fixed criteria value will not be changed while we use this for other criteria. We have to always change criteria manually.

(II). ByRef – =COUNTIF($A$2:$A$10,D2), this is flexible criteria value will change while using this for other criteria.

How to use COUNTIF function in Excel?

Rules for COUNTIF

(A). Non-Numeric criteria must be in double quotes i.e “Criteria”, but numeric criteria doesn’t require double quotes except with operator i.e “>180”.

(B). The wildcards (*, ?) can be used as criteria in double quotes. Star (*) represent any series of characters and question mark (?) to represent any single of characters.

(C). Criteria length should not be more than 255 characters.

 

COUNTIF Example

  1. COUNTIF with ByVal and ByRef example

how to use countif formula in excel

ByVal =COUNTIF($L$2:$L$9428,”Varanasi”). Varanasi is a ByVal criterion in this syntax and COUNTIF count the value from this Range $L$1:$L$9428 based on criteria.

how to use countif formula for multiple conditions

ByRef =COUNTIF($L$1:$L$9428,A3). A3 is a ByRef criterion in this syntax and COUNTIF count the value from this Range $L$1:$L$9428 based on criteria.

  1. COUNTIF with two criteria using sum

how to use countif function for multiple criteria

=SUM(COUNTIF($A$2:$A$9428,{“Varanasi”,”Jaipur”},$B$2:$B$21)) in this syntax COUNTIF count City by each criterion after that SUM add the number.

  1. Add Negative value from COUNTIF function

countif function for multiple criteria

=COUNTIF(A2:A26,”<0″), in this example we find the negative value from COUNTIF Function so we use operator (“<0”).

 

Download File

How to use Countifs function in Excel?

Leave a Reply

Your email address will not be published. Required fields are marked *