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.

Read More

COUNT

“COUNT”

Used for – “COUNT” function in MS Excel is used count the number of cell that contain numbers in given array or range.

Syntax – COUNT(value1, [value2], …)
             value1 – It is a cell / array  / range for which you wants to count the number of entries entered in number. It is a compulsory field and if you did not fill any value excel will not proceed further and if you enter only comma(,) in the formula excel will consider two values with number zero.

            [value2] – It is same as of value1 and is an optional field. You can add upto 255 values in this function.

Example – Following example will clear the COUNT function:-

In below table column A contains some data.

	A	B	C	D	E	F	G	H
1	25-09-2017		=COUNT(A1:A9)
2	51		=COUNT(A6:A8)
3	20.4		=COUNT(A1:A4)
4	excelwithease		=COUNT(A1:A3)
5	TRUE		=COUNT(A4:A9)
6	YES		=COUNT(A4:A9,2)
7	#VALUE!
8	#DIV/0!
9

Here cell A1 contains date, cell A2 & A3 contains numbers, cell A4 to A6 contains text values and cell A7 & A8 contains errors and column C contains count function for different selection.

And result you get as under:-

	A	B	C	D	E	F	G	H
1	25-09-2017		3
2	51		0
3	20.4		3
4	excelwithease		3
5	TRUE		0
6	YES		1
7	#VALUE!
8	#DIV/0!
9

NOTE :- Please do care and remember the following points whenever you use COUNT function :-

•  Only 255 values can be provided for COUNT function.
• Before using COUNT check all the numeric values are in number / date / time format if the values are in text format they will be ignored during the count.
• The text values or logical values or error values or empty cell will be ignored and are not count.

Read More