how to implement such function

C

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
 
A

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?
 
C

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
 
C

ccghzhu

Ardus said:
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?
Click to expand...
[/QUOTE]

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