# Shorten formula

#### j2curtis64

##### New Member
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)

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### xld

##### Banned
.

=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)

Replies
7
Views
306
Replies
3
Views
25
Replies
6
Views
194
Replies
2
Views
160
Replies
0
Views
305

### Forum statistics

1,191,689
Messages
5,988,032
Members
440,125
Latest member
vincentchu2369 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back