# COUNTIF

## “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.