Excluding Negative numbers in a sum formula.

[rhayhe]

Im trying to find out how to ignore negative values that on a row in my formula in excel.

Here is my formula.


=SUM(F12,H12,J12,L12,N12,P12,R12)

Is there a way to exclude any negative value that is in a cell?

Thank you.

 

[mikerickson]

If the intermediate cells have text values you could use =SUMIF(F12:R12, ">0")

 

[saelageonzon]

First we can add a row helper indicating columns we wanted to sum.

E.g.,

[TABLE="width: 896"]
<colgroup><col width="64" style="width: 48pt;"><col width="64" span="13" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="width: 64"]Columns[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[TD="class: xl65, width: 64"]K[/TD]
[TD="class: xl65, width: 64"]L[/TD]
[TD="class: xl65, width: 64"]M[/TD]
[TD="class: xl65, width: 64"]N[/TD]
[TD="class: xl65, width: 64"]O[/TD]
[TD="class: xl65, width: 64"]P[/TD]
[TD="class: xl65, width: 64"]Q[/TD]
[TD="class: xl65, width: 64"]R[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]1[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl66"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl67, align: right"]0.780824[/TD]
[TD="class: xl67, align: right"]-0.09437[/TD]
[TD="class: xl67, align: right"]0.904573[/TD]
[TD="class: xl67, align: right"]0.554762[/TD]
[TD="class: xl67, align: right"]-0.09173[/TD]
[TD="class: xl67, align: right"]-0.14365[/TD]
[TD="class: xl67, align: right"]0.186682[/TD]
[TD="class: xl67, align: right"]0.505822[/TD]
[TD="class: xl67, align: right"]0.120212[/TD]
[TD="class: xl67, align: right"]0.327678[/TD]
[TD="class: xl67, align: right"]0.544227[/TD]
[TD="class: xl67, align: right"]0.526816[/TD]
[TD="class: xl67, align: right"]0.851565[/TD]
[/TR]
</tbody>[/TABLE]

From there, we can use SUMIFS function

=SUMIFS($F$11:$R$11,$F$11:$R$11,">"&0,$F$10:$R$10,1)

 

[rhayhe]

I only need to add are the Row F12,H12,J12,L12,N12,P12, and R12

so the Formula F12:R12 wont do.

Is there still a way to exclude negative values when I sum the Cells?

 

[jarjarbingie]

i only need to add are the row f12,h12,j12,l12,n12,p12, and r12

so the formula f12:r12 wont do.

Is there still a way to exclude negative values when i sum the cells?

=sum(if(f12<0,0,f12),if(h12<0,0,h12),if(j12<0,0,j12),if(l12<0,0,l12),if(n12<0,0,n12),if(p12<0,0,p12),if(r12<0,0,r12))

 

[saelageonzon]

The sumifs function will exclude it.

=SUMIFS($F$12:$R$12,$F$12:$R$12,">"&0,$F$11:$R$11,1)
-the texts with red color is the sum range.
-the texts with blue color is the criteria range and criteria which excludes negative values.
-the texts with orange color is the criteria range and criteria which only includes the important columns.

Another alternative but lengthy formula will be using if statements:
=SUM(IF(F12>0,F12,0),IF(H12>0,H12,0),IF(J12>0,J12,0),IF(L12>0,L12,0),IF(N12>0,N12,0),IF(P12>0,P12,0),IF(R12>0,R12,0))

 

[rhayhe]

Thanks. the alternate but lengthy formula worked.

i get 0 whenever I use this
=SUMIFS($F$12:$R$12,$F$12:$R$12,">"&0,$F$11:$R$11,1)

 

[saelageonzon]

There should be an added column helper in =SUMIFS($F$12:$R$12,$F$12:$R$12,">"&0,$F$11:$R$11,1) formula.

Cool! You're welcome.