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

#### excelindianfanclub

##### Board Regular
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.

Also:

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

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

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

Replies
1
Views
542
Replies
6
Views
492
Replies
44
Views
5K
Replies
1
Views
2K
Replies
1
Views
2K

1,203,065
Messages
6,053,321
Members
444,653
Latest member
Curdood

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back