Count no. of occurences without counting ZEROES

[hsandeep]

I am counting 'no. of values' by using formula =COUNTIF(C59:C162,"?*")+COUNT(C59:C162).
But it also counts 0 (zero). I want that it should not count 0s occurred in cells.
How to accomplish?

 

[hsandeep]

The range had 5 0s generated throgh vlookup formula. So CORRECT ANSWER is 99 but it is giving WRONG ANSWER as 104!!!

 

[Nakes]

NO. It is still counting 0 occurences in the cells. These 0s are generated through vlookup formulas.

Perhaps changing the vlookup formula to IFERROR(vlookup,"")

 

[hsandeep]

I'll try & give u feedback

 

[hsandeep]

I tried. I need something like IFZERO(vlookup,""). Please help

 

[Nakes]

I tried. I need something like IFZERO(vlookup,""). Please help

Seems you answered your own question then..

IF(VLOOKUP=0,"",VLOOKUP)

 

[hsandeep]

IF(VLOOKUP=0,"",VLOOKUP)[/QUOTE]
I modified like above & used =COUNTIF(C59:C162,"?*")+COUNT(C59:C162)-COUNTIF(C59:C162,""), but still it counts 0 occurrences!!!

 

[Marcelo Branco]

Let me ask a foolish question:when do you click on a cell containing 0 what do you see in the formula bar?

M.

 

[circledchicken]

Hi,

Another possibility might be that the values are not really zero but some small amount and the formatting makes it look like zero. Perhaps see if this gives you a different answer:

=COUNTIF(C59:C162,"?*")+COUNT(C59:C162)-COUNTIF(C59:C162,"<1")

If not perhaps post some sample data or post your file on Box.com or SkyDrive and someone might be able to look at it.

 

[T. Valko]

Perhaps the formula is returning text. Maybe

=COUNTIF(C59:C162,"?*")+COUNT(C59:C162)-COUNTIF(C59:C162,"0")

COUNTIF evaluates text numbers and numeric numbers as being equal.

COUNTIF(range,0) and COUNTIF(range,"0") are the same.

Book1
	A
1	0
2	0
3	0
4	0
5	0
Sheet1

A4:A5 have been formatted as text.

=COUNTIF(A1:A5,0) = 5
=COUNTIF(A1:A5,"0") = 5

 

[Marcelo Branco]

Hi,

Another possibility might be that the values are not really zero but some small amount and the formatting makes it look like zero. Perhaps see if this gives you a different answer:

=COUNTIF(C59:C162,"?*")+COUNT(C59:C162)-COUNTIF(C59:C162,"<1")

If not perhaps post some sample data or post your file on Box.com or SkyDrive and someone might be able to look at it.

I also thought in something like this but as =COUNT(C59:C162) returns 0 (see #6) there are no numbers in the range (???)

M.