# Combining If And statements with multiple outcomes

#### jf0789

##### New Member
Hello, <o></o>
I have multiple stand-alone if and statements I wish to combine into one statement in excel if possible. Please help. <o></o>
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2+AW2+AY2)=0,BA2>0,BA2)<o></o>
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2+AW2)=0,AY2>0,AY2)<o></o>
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2)=0,AW2>0,AW2)<o></o>
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2)=0,AU2>0,AU2)<o></o>
Etc.<o></o>
Thanks,<o></o>
jf0789

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### oldbrewer

##### Board Regular
make a spare helper cell = q2+s2+u2...etc
then you can refer to AZ1, AZ1-AY2 .... etc

then it don't look so complex...

if(D1=3,if(E1=3,"yes","no"),"no") is the same as if(and(D1=3,E1=3,"yes","no") but 5 characters longer it does give the no output though

#### NeonRedSharpie

##### Well-known Member
Code:
``````=if(and((q2+s2+u2+w2+y2+aa2+ac2+ae2+ag2+ai2+ak2+am2+ao2+aq2+as2+au2+aw2+ay2)=0,ba2>0),ba2,if(and((q2+s2+u2+w2+y2+aa2+ac2+ae2+ag2+ai2+ak2+am2+ao2+aq2+as2+au2+aw2)=0,ay2>0),ay2,
if(and((q2+s2+u2+w2+y2+aa2+ac2+ae2+ag2+ai2+ak2+am2+ao2+aq2+as2+au2)=0,aw2>0),aw2,
if(and((q2+s2+u2+w2+y2+aa2+ac2+ae2+ag2+ai2+ak2+am2+ao2+aq2+as2)=0,au2>0),au2))))``````

#### Special-K99

##### Well-known Member
These dont make sense

=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2+AW2+AY2)=0,BA2>0,BA2)
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2+AW2)=0,AY2>0,AY2)
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2)=0,AW2>0,AW2)
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2)=0,AU2>0,AU2)

IF(blah blah blah)=0 then BA2>0 else BA2 ???????????
IF(blah blah blah)=0 then AY2>0 else AY2 ???????????
IF(blah blah blah)=0 then AW2>0 else AW2 ???????????
IF(blah blah blah)=0 then AU2>0 else AU2 ???????????

#### NeonRedSharpie

##### Well-known Member

These dont make sense

=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2+AW2+AY2)=0,BA2>0,BA2)
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2+AW2)=0,AY2>0,AY2)
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2+AU2)=0,AW2>0,AW2)
=IF(AND(Q2+S2+U2+W2+Y2+AA2+AC2+AE2+AG2+AI2+AK2+AM2+AO2+AQ2+AS2)=0,AU2>0,AU2)

IF(blah blah blah)=0 then BA2>0 ???????????
IF(blah blah blah)=0 then AY2>0 ???????????
IF(blah blah blah)=0 then AW2>0 ???????????
IF(blah blah blah)=0 then AU2>0 ???????????

He's just missing another pair of () around his AND statement. If you want to really be literal, it's saying:

IF(TRUE) = 0, BA2>0, BA2

#### oldbrewer

##### Board Regular
he is saying if true and if true, BA2
if both or either is false nothing appears to be returned, or am I missing something....

#### jasonb75

##### Well-known Member

Possible missing parenthesis aside, I read it as 'look for a cumulative total of zero and return the next positive value.

I've been trying to come up with a short, efficient, helper cell free formula, this is the only thing I came up with that worked.

Code:
``{=CHOOSE(MATCH(2,(SUBTOTAL(9,Q2,S2,U2,W2,Y2,AA2,AC2,AE2,AG2,AI2,AK2,AM2,AO2,AQ2,OFFSET(AS2,,,,{1,3,5,7}))=0)+(CHOOSE({1,2,3,4},AU2,AW2,AY2,BA2)>0),0),AU2,AW2,AY2,BA2)}``
Looks like I failed on short and efficient.

#### oldbrewer

##### Board Regular
if total is zero, AND BA2 >0 return BA2

original poster - is that what you want
or are you saying if above NOT true TRY A DIFFERENT TOTAL AND A DIFFERENT CELL >0

AND SO ON

#### oldbrewer

##### Board Regular
i THINK i KNOW WHAT HE WANTS

let numbers be 1....2......-3......4.....-5 total <>0 so look at first 4 numbers
1....2......-3.......4 total <>0 so look at first 3 numbers
1......2........-3 total is 0 so return AW2

the cell to be returned is 2 to the left of the previous candidate

#### oldbrewer

##### Board Regular
 1 2 -3 4 -5 not zero not zero 9999 not zero not zero your potential reference cells cell I14 999999 99999 9999 999 99 formula in F1 =IF(SUM(\$A\$1:E1)=0,OFFSET(\$I\$14,0,COUNT(\$A\$1:E1)+2),"not zero") this is copied to E2 and D3 and C4 and B5

<colgroup><col span="9"><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

Replies
4
Views
22
Replies
14
Views
668
Replies
1
Views
52
Replies
13
Views
343
Replies
2
Views
56