Help with a long Nested IF Statement with AND as well I think

[Badvgood]

Problem Solved.

[benishiryo]

hi there. 2 ways:
1.
=IF(COUNTIF(Z3:AB3,40)=1,40,IF(AND(AB3=0,Z3=40,AA3=40),80,IF(COUNTIF(Z3:AB3,40)=2,100,IF(COUNTIF(Z3:AB3,40)=3,150,0))))

2. do a list of your criteria. for eg in AG2:AI2 are your GAP, Ext.Warr, Paint.
AG3: 0
AH3: 0
AI3: 40
do a formula in AJ3:
=AG3&"_"&AH3&"_"&AI3
in AK3:
40
so it means this combination should return you 40. do another combination in row 4. that would be 40, 0, 0. in row 5 - 40, 0, 40.
then in AC3:

[Badvgood]

hi there. 2 ways:
1.
=IF(COUNTIF(Z3:AB3,40)=1,40,IF(AND(AB3=0,Z3=40,AA3=40),80,IF(COUNTIF(Z3:AB3,40)=2,100,IF(COUNTIF(Z3:AB3,40)=3,150,0))))

2. do a list of your criteria. for eg in AG2:AI2 are your GAP, Ext.Warr, Paint.
AG3: 0
AH3: 0
AI3: 40
do a formula in AJ3:
=AG3&"_"&AH3&"_"&AI3
in AK3:
40
so it means this combination should return you 40. do another combination in row 4. that would be 40, 0, 0. in row 5 - 40, 0, 40.
then in AC3:

Perfect, thank you so much.

[newdoverman]

Your blank cells are really 0 formatted to show -
This formula follows the language that you use to describe the criteria.
This entered in AC3 and filled down should cover all your possibilities

Please Login or Register  to view this content.

[=D=D]

Probably more elegant ways but I added column AC after Paint and moved your Total to column AD for this. Then in relevant rows in column AC copy:

=BIN2DEC(IF(Z3=$I$1,1,0)&IF(AA3=$J$1,1,0)&IF(AB3=$K$1,1,0))

This converts your various possible outcomes to a unique decimal case number 0-7 which you can then use in your nested IF statement in your Total column:

=IF(AC3=0,0,IF(AC3=1,40,IF(AC3=2,40,IF(AC3=3,100,IF(AC3=4,40,IF(AC3=5,100,IF(AC3=6,80,IF(AC3=7,150,0))))))))