MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Countif


Posted by Lewis on January 17, 2002 5:19 PM

Hi,
When I put this formula in a cell

=SUM(1/COUNTIF(C14:C52,C14:C52))-10

There is somthing I have to do when I deselect that cell inorder to avoind the div/0 error, can anyone refresh my memory as to what it is

thanks

-Lewis


Posted by Jacob on January 17, 2002 6:48 PM

Hi

Try this:

=If(COUNTIF(C14:C52,C14:C52)=0,0,SUM(1/COUNTIF(C14:C52,C14:C52))-10)

HTH

Jacob

Posted by Lewis on January 18, 2002 4:10 PM

Thanks Jacob, but that didn't work, I know that the formula I gave below worked before, because it is still working in the sheet, but I need to change the cells it is counting...

I know there was somthing you had to do with it to stop the div/0 error, I just can't remember what it was.

Thanks

-Lewis

Posted by Lewis on January 18, 2002 4:13 PM

Thanks Jacob, but that didn't work, I know that the formula I gave below worked before, because it is still working in the sheet, but I need to change the cells it is counting...

I know there was somthing you had to do with it to stop the div/0 error, I just can't remember what it was.

Thanks

-Lewis

Posted by Jacob on January 19, 2002 1:48 PM

Hi

To stop the Dix/0 you check to see if what is in the demoninator is 0 first if it is then display whatever value you want if not then do your calculation. You could also use conditional formatting to make the div/0 white text so it would be invisible.

HTH

Jacob