Simplifying multiple IF statements?

[rgbiernat]

Hi there,

In U3 I have this working formula:

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

In U4 I basically want to have the same formula but instead I want t
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 an
change the values but that's not really efficient.

Thanks,
Ruedige

 

[Living the Dream]

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.