Nested IF formulas

[epyzdrh]

I have the three conditions below that can be met,

Formula 1 =SUM(IF((F:F>0)*(I:I>0),K:K))
Formula 2 =SUM(IF((F:F>0)*(I:I=0),K:K))
Formula 3 =SUM(IF((F:F=0)*(I:I=0),K:K))

and what I would like to achieve is to have these in one cell but if Formula 1 is met the cell equals 1, if formula 2 is met the cell equals 0 and if Formula 3 is met the cell equals -1. Doesn't feel like I'm far away from the answer but I can't quite see it yet?

Dave

 

[prajul89]

I didn't understood from your formula what exactly you meant but try this:

=IF(1st condition,TRUE,IF(2nd condition,TRUE,IF(3rd condition,TRUE,FALSE)))

 

[Comfy]

=sum(if((f:f>0)*(i:i>0),1,sum(if((f:f>0)*(i:i=0),0,sum(if((f:f=0)*(i:i=0),-1))))))

 

[Aladin Akyurek]

I have the three conditions below that can be met,

Formula 1 =SUM(IF((F:F>0)*(I:I>0),K:K))
Formula 2 =SUM(IF((F:F>0)*(I:I=0),K:K))
Formula 3 =SUM(IF((F:F=0)*(I:I=0),K:K))

and what I would like to achieve is to have these in one cell but if Formula 1 is met the cell equals 1, if formula 2 is met the cell equals 0 and if Formula 3 is met the cell equals -1. Doesn't feel like I'm far away from the answer but I can't quite see it yet?

Dave

You intent is not very clear... If you want to coalesce them into a single formula:

Either: Control+shift+enter..

=SUM(
    IF(ISNUMBER($F$2:$F$100),
    IF($F$2:$F$100>=0,
    IF(ISNUMBER($I$2:$I$100),
    IF($I$2:$I$100>=0,
      $K$2:$K$100)))))

Or, just enter...

=SUMIFS($K$2:$K$100,$F$2:$F$100,">=0",$I$2:$I$100,">=0")