CODEWITHSUNDEEP
excelfunction
Paulo Pacheco
Paulo Pacheco

Reputation: 3

nested if-and excel cleaner way

Is there a cleaner way to do the following nested if-and function?

=IF($B$2="";"";IF(AND($B$1="EN 13286-2";$B$2="Pequeno");G5;IF(AND($B$1="EN 13286-2";$B$2="Grande");G6;IF(AND($B$1="ASTM";$B$2="Pequeno");G7;IF(AND($B$1="ASTM";$B$2="Grande");G8)))))

enter image description here

Basically I fill cells c1 and c4 and with the nested function if-and I get the correct result. From which AND combination, the outcome is different. This function works fine but I would to get it cleaner maybe with a different approach.

For the previous function the outcome is 3943,3

Thank you!

Upvotes: 0

Views: 79

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

If there is any text in the output from column G, you will want this formula:

=INDEX(C.A.!$G$6:$G$9;MATCH(1;INDEX(($C$1 = C.A.!$A$6:$A$9)*($C$4 = C.A.!$B$6:$B$9););0))

If the Output is number then a simple SUMIFS will work:

=SUMIFS(C.A.!G:G;C.A.!A:A;C1;C.A.!B:B;C4)

Upvotes: 1

gtwebb
gtwebb

Reputation: 3011

One way that can be used to copy over the numeric values, won't work on any text columns though.

=SUMPRODUCT(($C$1=C.A.!A$6:A$9)*($C$4=C.A.!B$6:B$9)*(C.A.!G$6:G$9))

Upvotes: 1

Related Questions