Add if statement to SumIfs

[bgonen]

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"}))

 

[Aladin Akyurek]

Which number are we talking about? Do we really need SUM round, since the criteria are both single member sets?

 

[bgonen]

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"})

 

[Aladin Akyurek]

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

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

 

[bgonen]

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.

 

[Aladin Akyurek]

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.

 

[bgonen]

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

 

[Aladin Akyurek]

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

De rien, as the French would say.