## How to use Countif Function in Excel

### 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.   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 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. 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 =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(A2:A26,”<0″), in this example we find the negative value from COUNTIF Function so we use operator (“<0”).