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.,

Columns	F	G	H	I	J	K	L	M	N	O	P	Q	R
	1		1		1		1		1		1		1
	0.780824	-0.09437	0.904573	0.554762	-0.09173	-0.14365	0.186682	0.505822	0.120212	0.327678	0.544227	0.526816	0.851565

<colgroup><col width="64" style="width: 48pt;"><col width="64" span="13" style="width: 48pt;"></colgroup><tbody>
</tbody>

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]

ADVERTISEMENT

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.