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?

 

[Andrew Poulsom]

Like this?

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

 

[hsandeep]

I tried. But still your formula counted 0s occurred in the range!!!

 

[Aladin Akyurek]

I tried. But still your formula counted 0s occurred in the range!!!

The formula Andrew suggests should succeed. What is the result that you get with it and do the follwing return?

=COUNTIF(C59:C162,"?*")

=COUNT(C59:C162)

=COUNTIF(C59:C162,0)

=SUMPRODUCT((C59:C162=0)+0)

 

[pgc01]

Hi

Can you post a sample of your data?

You should always post a sample of your data, that we can use for testing. It makes it easier to avoid misunserstandings.

 

[hsandeep]

=COUNTIF(C59:C162,"?*") 104 (All 104 cells had values & not equal to 0).

=COUNT(C59:C162) Answer=0


=COUNTIF(C59:C162,0)
Answer=0
=SUMPRODUCT((C59:C162=0)+0) Answer=0

 

[Aladin Akyurek]

=COUNTIF(C59:C162,"?*") 104 (All 104 cells had values & not equal to 0).

=COUNT(C59:C162) Answer=0


=COUNTIF(C59:C162,0)
Answer=0
=SUMPRODUCT((C59:C162=0)+0) Answer=0

So no numbers, 0 included.

Conclusion:

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

Andrew suggested will/must return: 104.

 

[hsandeep]

I am getting 0s in the cells through formula generated. Can this be the reason that the formula is NOT WORKING?

 

[VoG]

Perhaps the formula is returning text. Maybe

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

 

[hsandeep]

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