Countif

[chemexhants]

I am attempting to use the COUNTIF function to count the number of cells in a column containing '0' (zero) but the command also counts cells containing a number including a zero, eg. 82.90.

Is there an easy way to get around this or should I be using a different function?

I am using Excel 2007 and XP.

Stephen

 

[RoryA]

Can I ask why they contain hyperlinks if they are values?

 

[chemexhants]

Thought this was the way to show data on one worksheet that was automatically copied from another . Is there a better command to do this?

 

[RoryA]

If you just want to link to a cell on another sheet, it's just:
='Sheet name'!A1
for example.

 

[chemexhants]

Again, thank you for your patience.

I have used the formula suggested. This provides blank cells where the original data is zero. If I use =COUNTIF(range,0) it still counts all cells where the numbers end with zero. If I use =COUNTBLANK(range) I get a dash.

 

[RoryA]

Any chance you can post a (suitably censored) workbook somewhere (eg box.net) as I think something must be getting lost in translation.

 

[RoryA]

On your sample sheet, =COUNTIF(C8:C18,0) returns 4 as it should (remembering there are 3 hidden rows, which return 0).

 

[chemexhants]

Rory

Thank you. Is there a way to ignore the hidden rows?

 

[RoryA]

Yes:
=SUM(IF(SUBTOTAL(103,OFFSET(C8:C18,ROW(C8:C18)-MIN(ROW(C8:C18)),,1)),IF(C8:C18=0,1,0),""))

needs to be entered with Ctrl+Shift+Enter rather than just Enter (you should see the formula get enclosed in {} but you don't type those yourself)

 

[chemexhants]

Brilliant!

Rory, thank you so much for your help.

 

[RoryA]

Glad to help.