Shorten formula

[j2curtis64]

Hi,

I am trying to do the following in excel but i am having a hard time. The formula is going to be too long.

I have two columns where i input data. One column (B)is GOV or KTR and the other column (C) you could have L, O, or T. The next column i concatenate columns B+C. The value will determine the cell referenced in the other sheet (rate_table). After the cell that is referenced from the rate_table that amount is multipled by the columns referenced in the SUM function.

Any quicker or efficient way to do this?

=IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(J22:M22)+

IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(N22:Q22)+

IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(R22:U22)

+IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(V22:Y22)

Thanks for your assistance

 

[xld]

.

=IF($D$22="GOVL",Rate_Table!F6,IF(OR($D$22="GOVO",$D$22="GOVT",$D$22="KTRL",$D$22="KTRO"),Rate_Table!F10))*SUM(J22:M22)
+IF($D$22="GOVL",Rate_Table!F6,IF(OR($D$22="GOVO",$D$22="GOVT",$D$22="KTRL",$D$22="KTRO"),Rate_Table!F10))*SUM(N22:Q22)
+IF($D$22="GOVL",Rate_Table!F6,IF(OR($D$22="GOVO",$D$22="GOVT",$D$22="KTRL",$D$22="KTRO"),Rate_Table!F10))*SUM(R22:U22)
+IF($D$22="GOVL",Rate_Table!F6,IF(OR($D$22="GOVO",$D$22="GOVT",$D$22="KTRL",$D$22="KTRO"),Rate_Table!F10))*SUM(V22:Y22)