# IF Statement - Please help!

#### owgarth

##### New Member
Hello,

I have a long if statement which I would like to shorten, I have an idea on how I want to do this but am not sure if it is possible.

In short: I would like to: IF(A1=B1, C1, [value_if_true]*D1)

My [value_if_true], is a long formula and my [value_if_false] is simply multiflying [value_if_true] by another cell/formula. Is it possible to get excel to return [value_if_true] in the [value_if_false] without just duplicating it?

Thanks ### Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### mole999

##### Moderator
post your formula, someone maybe able to shorten it

#### owgarth

##### New Member
post your formula, someone maybe able to shorten it

Hi mole999
Here is one of the formulas...

=IF(\$B\$4>=F\$6,IFERROR(IF(\$B\$5="Local Currency:",IF(\$B\$2="GLOBAL TOTAL",-SUMIFS(Data!\$M:\$M,Data!\$E:\$E,F\$6,Data!\$F:\$F,"ACTUALS",Data!\$O:\$O,\$B11),IF(LEFT(\$B\$2,3)="XXX",-SUMIF(Data!\$A:\$A,F\$6&"ACTUALS"&\$B\$2&\$B11,Data!\$L:\$L),-SUMIFS(Data!\$L:\$L,Data!\$E:\$E,F\$6,Data!\$F:\$F,"ACTUALS",Data!\$G:\$G,LEFT(\$B\$2,4),Data!\$O:\$O,\$B11))),IF(\$B\$2="GLOBAL TOTAL",-SUMIFS(Data!\$M:\$M,Data!\$E:\$E,F\$6,Data!\$F:\$F,"ACTUALS",Data!\$O:\$O,\$B11),IF(LEFT(\$B\$2,3)="XXX",-SUMIF(Data!\$A:\$A,F\$6&"ACTUALS"&\$B\$2&\$B11,Data!\$L:\$L),-SUMIFS(Data!\$L:\$L,Data!\$E:\$E,F\$6,Data!\$F:\$F,"ACTUALS",Data!\$G:\$G,LEFT(\$B\$2,4),Data!\$O:\$O,\$B11)))*F\$5),0),0)

The green text is the same as the red but *F\$5.
If anyone would know a way to shorten this, or if it is possible to return the ture value out of an if satatement, without repeating the formula?

Thanks #### owgarth

##### New Member
I would like the formula to be something like this:

=IF(\$B\$4>=F\$6,IFERROR(IF(\$B\$5="Local Currency:",IF(\$B\$2="GLOBAL TOTAL",-SUMIFS(Data!\$M:\$M,Data!\$E:\$E,F\$6,Data!\$F:\$F,"ACTUALS",Data!\$O:\$O,\$B11),IF(LEFT(\$B\$2,3)="XXX",-SUMIF(Data!\$A:\$A,F\$6&"ACTUALS"&\$B\$2&\$B11,Data!\$L:\$L),-SUMIFS(Data!\$L:\$L,Data!\$E:\$E,F\$6,Data!\$F:\$F,"ACTUALS",Data!\$G:\$G,LEFT(\$B\$2,4),Data!\$O:\$O,\$B11))),[value_if_true]*F\$5),0),0)

But this does not work...

#### MarcelBeug

##### Well-known Member
Edit: this was posted before I noticed the previous post.

In general it is possible to have an IF-statement in a formula like:
Code:
``=IF(condition; value-if-true; value-if-false) * formula``
So you get either value-if-true * formula or value-if-false * formula.
I guess in your case the value-if-true would be 1 and the value-if-false would be F\$5, but I didn't go into the details of your formula.

#### owgarth

##### New Member
Thanks MarcelBeug!

Obviously overthinking this, I have rearranged it, which is shorter. Thanks very much!

=IF(\$B\$4>=F\$6,IFERROR(IF(\$B\$2="GLOBAL TOTAL",-SUMIFS(Data!\$M:\$M,Data!\$E:\$E,F\$6,Data!\$F:\$F,"ACTUALS",Data!\$O:\$O,\$B11),IF(LEFT(\$B\$2,3)="XXX",-SUMIF(Data!\$A:\$A,F\$6&"ACTUALS"&\$B\$2&\$B11,Data!\$L:\$L),-SUMIFS(Data!\$L:\$L,Data!\$E:\$E,F\$6,Data!\$F:\$F,"ACTUALS",Data!\$G:\$G,LEFT(\$B\$2,4),Data!\$O:\$O,\$B11)))*(IF(\$B\$5="Local Currency:",1,F\$5)),0),0)

So I guess there is not a way to return the true value as I described.

Replies
6
Views
597
Replies
1
Views
76
Replies
6
Views
122
Replies
4
Views
132
Replies
1
Views
161

Threads
1,127,605
Messages
5,625,762
Members
416,136
Latest member
senthil_sk

### Share this page ### 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.
Allow Ads at MrExcel

### Which adblocker are you using?    ### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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