how to implement such function

[ccghzhu]

the function is sum the number of positive
for example
input 5 column 3, 0 , 4 ,2, 0
result : 3


I try to use
=SUM( IF(A1:E1>0,1,0))

the return result is not the desired.
Although VBA can help solving this problem, but I hope that
someone have more easy method to solve this problem.

Sincerely,
Zhu

 

[Ardus Petus]

In your example, 3, 4 and 2 are positive.
So the result is 3+4+2=9

How come you expect the result to be 2 ?
Maybe you mean AVERAGE?

 

[Bondi]

Hi Zhu,

You could try something like

=COUNTIF(A1:E1,">0")

Regards,
Bondi

 

[Chip Pearson]

Try the following array formula

=SUM(IF(A1:E1>0,A1:E1,0))

Since this is an array formula, you must press CTRL+SHIFT+ENTER
rather than just ENTER when you first enter the function and
whenever you edit it later. If you do this properly, Excel will
display the formula enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[ccghzhu]

Hi Zhu,

You could try something like

=COUNTIF(A1:E1,">0")

Regards,
Bondi

Thank you for your answer

 

[ccghzhu]

In your example, 3, 4 and 2 are positive.
So the result is 3+4+2=9

How come you expect the result to be 2 ?
Maybe you mean AVERAGE?

[/QUOTE]

the formalue is total the number of elements , not sum the
elements.
Thank you for you reply
Zhu