Simplifying multiple IF statements?
 

Hi there,

In U3 I have this working formula:

=IF(B3=("Y");'System details'!E7)+IF(C3=("Y");'System details'!E14)+IF(D3=("Y");'System details'!E21)+IF(E3=("Y");'System details'!E28)+IF(F3=("Y");'System details'!E35)+IF(G3=("Y");'System details'!E44)+IF(H3=("Y");'System details'!E51)+IF(I3=("Y");'System details'!E58)+IF(J3=("Y");'System details'!E65)+IF(K3=("Y");'System details'!E72)+IF(L3=("Y");'System details'!E79)+IF(M3=("Y");'System details'!E88)+IF(N3=("Y");'System details'!E95)+IF(O3=("Y");'System details'!E100)+IF(P3=("Y");'System details'!E109)+IF(Q3=("Y");'System details'!E118)+IF(R3=("Y");'System details'!E126)

In U4 I basically want to have the same formula but instead I want to have B4, C4, D4, etc. etc.

Is there any way to simplify my formula?

The same goes for U5 to U15. Of course I could just copy the formula and change the values but that's not really efficient. :-)

Thanks,
Ruediger

Simplifying multiple IF statements?
 

Hi Ruediger

You don't mention if you want the additional formula to include the same
'System Details' references.

Do these references have predetermined values that add up a score, if so
then use the following:

=IF($B3=("Y"),'System Details'!$E$7)+IF($C3=("Y"),'System
Details'!$E$14)+IF($D3=("Y"),'System
Details'!$E$21)+IF($E3=("Y"),'System
Details'!$E$28)+IF($F3=("Y"),'System
Details'!$E$35)+IF($G3=("Y"),'System Details'!$E$44)+IF($H3=("Y"),
'System Details'!$E$51)+IF($I3=("Y"),'System
Details'!$E$58)+IF($J3=("Y"),'System
Details'!$E$65)+IF($K3=("Y"),'System
Details'!$E$72)+IF($L3=("Y"),'System
Details'!$E$79)+IF($M3=("Y"),'System
Details'!$E$88)+IF($N3=("Y"),'System
Details'!$E$95)+IF($O3=("Y"),'System
Details'!$E$100)+IF($P3=("Y"),'System
Details'!$E$109)+IF($Q3=("Y"),'System
Details'!$E$118)+IF($R3=("Y"),'System Details'!$E$126)

NOTE: the change from Semi-Colon (;) to Comma (,) also the ($) -
absolute reference to each of your original System Details Cells and the
absolute reference for the columns in your main sheet.

You can copy/Paste this down as far as you like.

HTH
Mick.