“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.
Dear readers, after reading the Content please ask for advice and to provide constructive feedback Please Write Relevant Comment with Polite Language.Your comments inspired me to continue blogging. Your opinion much more valuable to me. Thank you.