Excluding Negative numbers in a sum formula.

rhayhe

New Member
Joined
Nov 25, 2012
Messages
3
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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,919
If the intermediate cells have text values you could use =SUMIF(F12:R12, ">0")
 

saelageonzon

Board Regular
Joined
Nov 16, 2012
Messages
50
First we can add a row helper indicating columns we wanted to sum.

E.g.,

ColumnsFGHIJKLMNOPQR
1 1 1 1 1 1 1
0.780824-0.094370.9045730.554762-0.09173-0.143650.1866820.5058220.1202120.3276780.5442270.5268160.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

New Member
Joined
Nov 25, 2012
Messages
3
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

Well-known Member
Joined
Nov 15, 2012
Messages
607

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

Board Regular
Joined
Nov 16, 2012
Messages
50
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

New Member
Joined
Nov 25, 2012
Messages
3
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

Board Regular
Joined
Nov 16, 2012
Messages
50
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,312
Messages
5,600,887
Members
414,414
Latest member
neil_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top