Combining If And statements with multiple outcomes

jf0789

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

Some videos you may like

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
Joined
Apr 11, 2010
Messages
11,003
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
Joined
Jul 14, 2014
Messages
1,678
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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Jul 14, 2014
Messages
1,678

ADVERTISEMENT

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
Joined
Apr 11, 2010
Messages
11,003
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
Joined
Dec 30, 2008
Messages
10,845
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Apr 11, 2010
Messages
11,003
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
Joined
Apr 11, 2010
Messages
11,003
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
Joined
Apr 11, 2010
Messages
11,003
12-34-5not zero
not zero
9999
not zero
not zero
your potential reference cells
cell I1499999999999999999999
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,105
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top