Clarification on Countif function.Might be Guru's help needed

[excelindianfanclub]

Hi Excelfans and ExcelGurus,
I need a clarification regarding Countif function for the following sample data of mine

		I	J	Formula
5		Numeric Value	111
6		Numeric Value stored as text	111	'111
7		Date	18 May 2003
8		Double quote blank		=""
9		Character space		=CHAR(1)
10		Empty cells
11		Error Values	#N/A	=NA()
12		Formula resultant blank		=IF(5<3,"Yes 5<3","")
13		Text	asas
14		Numeric Value enclosed in double quotes	1	="1"
17		Non Empty Cells	10	=COUNTIF(J5:J14,"<>""")
18		Non Empty Cells	9	=COUNTIF(J5:J14,"<>"&"")
19		Non Empty Cells	7	=SUMPRODUCT(--IFERROR(--(J5:J14<>""),1))

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

i want to count the non blank cells.i used the countif function but it is giving wrong answer.Even i though i got the correct answer using sumproduct.i need a clarification why Countif unable to count Non blank cells.is there correction needed in my formula?.i need a explanation for the above countif functions which i have used.How the Countif function worked in my case?.Please help me.Im going to burst if im not getting the reason.Thanks in advance

 

[Aladin Akyurek]

Hi Excelfans and ExcelGurus,
I need a clarification regarding Countif function for the following sample data of mine

		I	J	Formula
5		Numeric Value	111
6		Numeric Value stored as text	111	'111
7		Date	18 May 2003
8		Double quote blank		=""
9		Character space		=CHAR(1)
10		Empty cells
11		Error Values	#N/A	=NA()
12		Formula resultant blank		=IF(5<3,"Yes 5<3","")
13		Text	asas
14		Numeric Value enclosed in double quotes	1	="1"
17		Non Empty Cells	10	=COUNTIF(J5:J14,"<>""")
18		Non Empty Cells	9	=COUNTIF(J5:J14,"<>"&"")
19		Non Empty Cells	7	=SUMPRODUCT(--IFERROR(--(J5:J14<>""),1))

<tbody>
</tbody>

i want to count the non blank cells.i used the countif function but it is giving wrong answer.Even i though i got the correct answer using sumproduct.i need a clarification why Countif unable to count Non blank cells.is there correction needed in my formula?.i need a explanation for the above countif functions which i have used.How the Countif function worked in my case?.Please help me.Im going to burst if im not getting the reason.Thanks in advance

The condition part of:

=COUNTIF(J5:J14,"<>""")

is ill-formed, i.e., "<>""" is unparsable, hence (I guess) a count of all cells/records.

=COUNTIF(J5:J14,"<>"&"")

is equivalent to:

=COUNTIF(J5:J14,"<>")

This counts in the formula blanks, created with something like ="" or =IF(5<3,"Yes 5<3",""). Hence 9.

{=SUMPRODUCT(--IFERROR(--(J5:J14<>""),1))}

is equivalent to:

{=SUM(IF(ISERROR(J5:J14),1,IF(1-(J5:J14=""),1)))}

will exclude the empty cell and the cells with a formula blank, hence 7.

 

[Aladin Akyurek]

Also:

=ROWS(J5:J14)-COUNTIF(J5:J14,"")

should be equivalent to SumProduct or Sum/If, producing 7.

 

[excelindianfanclub]

Thanks for explanation.I learned two things
First thing is "<>""" a unparsable
Second thing "<>" counts even blanks created by Formula (or) =""

I love your 2 tricks given

MrExcel's MVPs always Rock...

And finally i like your Quote"Assuming too much and Qualifying too much are two faces of the same problem"... haha