# Add if statement to SumIfs

#### bgonen

##### Active Member
I'm trying to modify the below formula
If the number I'm trying to find is zero then I need it in the following format : 0.000001

=SUM(SUMIFS([Data.xlsm]SAP!\$S:\$S,[Data.xlsm]SAP!\$F:\$F,{"9001500"},[Data.xlsm]SAP!\$H:\$H,{"GEN098"}))

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Which number are we talking about? Do we really need SUM round, since the criteria are both single member sets?

Not sure why I had SUM but here is the revised formula; it either sum the matched amount (column S) or if no match, then zero (However, if zero then I'm trying to get 0.000001 ...not sure this is possible)

SUMIFS([Data.xlsm]SAP!\$S:\$S,[Data.xlsm]SAP!\$F:\$F,{"9001500"},[Data.xlsm]SAP!\$H:\$H,{"GEN098"})

Not sure why I had SUM but here is the revised formula; it either sum the matched amount (column S) or if no match, then zero (However, if zero then I'm trying to get 0.000001 ...not sure this is possible)

SUMIFS([Data.xlsm]SAP!\$S:\$S,[Data.xlsm]SAP!\$F:\$F,{"9001500"},[Data.xlsm]SAP!\$H:\$H,{"GEN098"})

That is,instead of 0 result, 0.000001, otheerwise the sum that obtains. If so...
Rich (BB code):
``````
=MAX(
0.000001,
SUMIFS([Data.xlsm]SAP!\$S:\$S,[Data.xlsm]SAP!\$F:\$F,9001500,[Data.xlsm]SAP!\$H:\$H,"GEN098"))
``````

Is this what you are after?

wow, this is perfect when I copy/paste/specialValues I get the 0.00001 That's exactly what I need.
Not sure what the Max do but it works perfectly.
Thank you very much.

wow, this is perfect when I copy/paste/specialValues I get the 0.00001 That's exactly what I need.
Not sure what the Max do but it works perfectly.

Let SUMIFS return 10...

MAX(0.000001,10)

MAX will pick out the highest of 0.000001 and 10. That's 10.

When SUMIFS returns 0 ( < 0 ), we get:

MAX(0.000001,0)

This picks out of course 0.000001.

Thank you very much.

You are welcome.

Amazing, Thanks a lot
I enjoyed both your detailed and simplified method of explaining this.

Amazing, Thanks a lot
I enjoyed both your detailed and simplified method of explaining this.

De rien, as the French would say.

Replies
2
Views
93
Replies
5
Views
207
Replies
7
Views
346
Replies
1
Views
855
Replies
4
Views
192

1,207,402
Messages
6,078,268
Members
446,324
Latest member
JKamlet

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

### Which adblocker are you using?

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

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