“COUNTIF”
Used for – “COUNTIF” function in MS Excel is used to count the number of cell
that match the criteria given by you in given array or range.
Syntax – COUNTIF(range, criteria)
range – It is a array within which you
want to give the criteria, based upon which you want to count the number of
cell. In this range each cell must contain a number, alphabets or alphanumeric
values which can be used as criteria. It is a compulsory field and if you did
not fill any value excel will not proceed further.
criteria – As
the name indicates it is a cell or value or condition or expression or a
function/formula that defines which cells to be added. It is a compulsory field
and if you did not fill any value excel will not proceed further.
Example – Following example will clear the COUNTIF
function:
In below table column A, B, & C
contains some sales data for particular item in specified cities. Now column F
contains the COUNTIF function with different criteria.
A

B

C

D

E

F

G

H

I


1

City

ITEM

Sale


2

New Delhi

Orange

5000

Orange

=COUNTIF(B2:B7,E2)


3

New Delhi

Banana

2000

=COUNTIF(C2:C7,">2000")


4

Muktsar

Papaya

1000

=COUNTIF(C2:C7,"=2000")


5

Muktsar

Orange

3000

=COUNTIF(B2:B7,"*a")


6

Chandigarh

Banana

4000


7

Chandigarh

Papaya

2000

Cell
F2 contains the no. of cites in which orange have been sold, cell F4 contains how
many times sale is more than 2000, while the cell F4 contains the number of how
many time sale is equal to 2000, cell F5 contains how many times items were
sold which ends with alphabet “a”.
And
result you get as under:
A

B

C

D

E

F

G

H

I


1

City

ITEM

Sale


2

New Delhi

Orange

5000

Orange

2


3

New Delhi

Banana

2000

3


4

Muktsar

Papaya

1000

2


5

Muktsar

Orange

3000

4


6

Chandigarh

Banana

4000


7

Chandigarh

Papaya

2000

NOTE
: Please
do care of following points when you use COUNTIF :
·
COUNTIF has the limitation that you can match only 255 characters
if you match more than it result incorrect.
·
Criteria must be entered in quotes otherwise #VALUE! Error may be
there.
If this function is
used in reference to other workbook then please ensure the other workbook is
open also or you may face #VALUE! error.