Discussion Re: Using a Sum function within a IF function in Excel
https://techcommunity.microsoft.com/t5/excel/using-a-sum-function-within-a-if-function/m-p/957522#M42888
<P><LI-USER uid="434058"></LI-USER> </P>
<P> </P>
<P>It should work, are you just using Copy Paste (Ctrl C, Ctrl V) ?</P>
<P> </P>Sun, 27 Oct 2019 11:43:44 GMTWyn Hopkins2019-10-27T11:43:44ZUsing a Sum function within a IF function
https://techcommunity.microsoft.com/t5/excel/using-a-sum-function-within-a-if-function/m-p/957108#M42865
<P>Hello, I have possibly a simple question. I have developed a =IF that contains a SUM function. when I attempt to copy it down my spreadsheet, the cells in the SUM statement become absolute I would like them to adjust to the new corresponding cells as I copy down. What do I do. Here is the Function</P><P>=IF(E65*G65<0,SUM((((ABS(G67*100)*ABS(E67))))+(((100*M67)*K67)+((100*Q67)*O67)+((100*U67*S67))+((100*Y67)*W67))),"ea")</P><P> </P><P>The E65*G65 adjust accordingly during the copy and paste the rest do not.</P><P> </P><P>Thankyou</P>Sat, 26 Oct 2019 20:50:59 GMThttps://techcommunity.microsoft.com/t5/excel/using-a-sum-function-within-a-if-function/m-p/957108#M42865Hopgrower2019-10-26T20:50:59ZRe: Using a Sum function within a IF function
https://techcommunity.microsoft.com/t5/excel/using-a-sum-function-within-a-if-function/m-p/957482#M42880
<P><LI-USER uid="434058"></LI-USER> </P><P>Entered your formula in a new workbook and copied it down a few rows. None of the cell references became absolute for me. Perhaps you can upload your workbook to demonstrate the problem.</P><P> </P><P>By the way, you may want to make your formula a bit easier to read and maintain. The SUM statement is not needed and it will reduce the number of <SPAN>brackets. </SPAN>I think this one will achieve the same result (with row 1 as the base in stead of 65 in your example);</P><P> </P><P>=IF(E1*G1<0,ABS(G3*100)*ABS(E3)+100*M3*K3+100*Q3*O3+100*U3*S3+100*Y3*W3,"ea")</P><P> </P><P> </P>Sun, 27 Oct 2019 09:24:50 GMThttps://techcommunity.microsoft.com/t5/excel/using-a-sum-function-within-a-if-function/m-p/957482#M42880Riny_van_Eekelen2019-10-27T09:24:50ZRe: Using a Sum function within a IF function
https://techcommunity.microsoft.com/t5/excel/using-a-sum-function-within-a-if-function/m-p/957522#M42888
<P><LI-USER uid="434058"></LI-USER> </P>
<P> </P>
<P>It should work, are you just using Copy Paste (Ctrl C, Ctrl V) ?</P>
<P> </P>Sun, 27 Oct 2019 11:43:44 GMThttps://techcommunity.microsoft.com/t5/excel/using-a-sum-function-within-a-if-function/m-p/957522#M42888Wyn Hopkins2019-10-27T11:43:44ZRe: Using a Sum function within a IF function
https://techcommunity.microsoft.com/t5/excel/using-a-sum-function-within-a-if-function/m-p/958066#M42927
<P><LI-USER uid="434058"></LI-USER> </P>
<P>I guess your formula could be simplified to</P>
<LI-CODE lang="markup">=IF(E65*G65<0,100*(ABS(G67*E67)+M67*K67+Q67*O67+U67*S67+Y67*W67),"ea")</LI-CODE>
<P>if copy/paste one row down it will be as</P>
<LI-CODE lang="markup">=IF(E66*G66<0,100*(ABS(G68*E68)+M68*K68+Q68*O68+U68*S68+Y68*W68),"ea")</LI-CODE>
<P>if that's what do you mean. Your initial formula with SUM shall return exactly the same result.</P>Mon, 28 Oct 2019 02:55:24 GMThttps://techcommunity.microsoft.com/t5/excel/using-a-sum-function-within-a-if-function/m-p/958066#M42927Sergei Baklan2019-10-28T02:55:24Z