Help needed with nested IF formula to total up

[OTSD1983]

Hi all,

I've been stuck on an Excel problem for a few hours now, I'm basically creating a template cost sheet for my company but on one of the sheets that calculates machining hours and costs I cannot get a column to add all of the other columns up.

Basically (trying to keep this as simple as possible) each line has up to 4 machining operations as follows:

• Each operation consists of an operation name in the first column selected from a dropdown list
• The second column is the hourly rate which is auto populated from the first column depending on the operation via a lookup formula
• The third column is manual entry, and is the amount of machining hours
• The fourth column is something I've just added which is where my problems have started, i've just called it manual multiplier for now and it's a manual entry box, essentially it just multiplies the sencond and third column

At the end of each line I need a total box, however as the user could leave some cells empty I need the 'total' formula to work regardless of whether cells are populated.

I've added an excerpt from the sheet below which will hopefully get across what I'm doing, I can send this via email if required too.

I can get one set of operations to work fine by using this formula:

=IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6))))

The question is how do I extend this to the other operation groups? Every time I try basically extending this formula it gives me the too many arguments error.

I'd greatly appreciate anyones assistance with this before I pull my hair out.

Thanks in advance


[TABLE="width: 2193"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Operation 1[/TD]
[TD="colspan: 4"]Operation 2[/TD]
[TD="colspan: 4"]Operation 3[/TD]
[TD="colspan: 4"]Operation 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line[/TD]
[TD]Part Description[/TD]
[TD]Operation Name[/TD]
[TD]Hourly Rate[/TD]
[TD]Machining Hours[/TD]
[TD]Manual Multiplier[/TD]
[TD]Operation Name[/TD]
[TD]Hourly Rate[/TD]
[TD]Machining Hours[/TD]
[TD]Manual Multiplier[/TD]
[TD]Operation Name[/TD]
[TD]Hourly Rate[/TD]
[TD]Machining Hours[/TD]
[TD]Manual Multiplier[/TD]
[TD]Operation Name[/TD]
[TD]Hourly Rate[/TD]
[TD]Machining Hours[/TD]
[TD]Manual Multiplier[/TD]
[TD]Total In-Process Cost[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Boring[/TD]
[TD="align: right"]£77.94[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD]Fitting[/TD]
[TD="align: right"]£48.64[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD]Boring[/TD]
[TD="align: right"]£77.94[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD]Boring[/TD]
[TD="align: right"]£77.94[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]£7,794[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col></colgroup>[/TABLE]

 

[SteveO59L]

would you not simply sum the total values for each of the operations?

 

[chrismacldn]

Hi all,

I've been stuck on an Excel problem for a few hours now, I'm basically creating a template cost sheet for my company but on one of the sheets that calculates machining hours and costs I cannot get a column to add all of the other columns up.

Basically (trying to keep this as simple as possible) each line has up to 4 machining operations as follows:

• Each operation consists of an operation name in the first column selected from a dropdown list
• The second column is the hourly rate which is auto populated from the first column depending on the operation via a lookup formula
• The third column is manual entry, and is the amount of machining hours
• The fourth column is something I've just added which is where my problems have started, i've just called it manual multiplier for now and it's a manual entry box, essentially it just multiplies the sencond and third column

At the end of each line I need a total box, however as the user could leave some cells empty I need the 'total' formula to work regardless of whether cells are populated.

I've added an excerpt from the sheet below which will hopefully get across what I'm doing, I can send this via email if required too.

I can get one set of operations to work fine by using this formula:

=IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6))))

The question is how do I extend this to the other operation groups? Every time I try basically extending this formula it gives me the too many arguments error.

I'd greatly appreciate anyones assistance with this before I pull my hair out.

Thanks in advance


[TABLE="width: 2193"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Operation 1[/TD]
[TD="colspan: 4"]Operation 2[/TD]
[TD="colspan: 4"]Operation 3[/TD]
[TD="colspan: 4"]Operation 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line[/TD]
[TD]Part Description[/TD]
[TD]Operation Name[/TD]
[TD]Hourly Rate[/TD]
[TD]Machining Hours[/TD]
[TD]Manual Multiplier[/TD]
[TD]Operation Name[/TD]
[TD]Hourly Rate[/TD]
[TD]Machining Hours[/TD]
[TD]Manual Multiplier[/TD]
[TD]Operation Name[/TD]
[TD]Hourly Rate[/TD]
[TD]Machining Hours[/TD]
[TD]Manual Multiplier[/TD]
[TD]Operation Name[/TD]
[TD]Hourly Rate[/TD]
[TD]Machining Hours[/TD]
[TD]Manual Multiplier[/TD]
[TD]Total In-Process Cost[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Boring[/TD]
[TD="align: right"]£77.94[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD]Fitting[/TD]
[TD="align: right"]£48.64[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD]Boring[/TD]
[TD="align: right"]£77.94[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD]Boring[/TD]
[TD="align: right"]£77.94[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]£7,794[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Drop file here > Free File Hosting - Online Storage; Upload Mp3, Videos, Music. Backup Files

I'll crack this for you

 

[OTSD1983]

Hi Chrismacldn,

Thanks for the quick response and sorry for the delay, I've just uploaded it now - http://www.filedropper.com/costsheettemplate

If you need any clarification on what i'm trying to achieve let me know, hoping you can crack this because I'm running out of hair! :D

Thanks

 

[chrismacldn]

Ok so you've managed to do your calculations for Operations 4 which are displayed in "Total In-Process Cost", now what you want is the "Total In-Process Cost" to do the same calculatios but for all 4 operation?

If so, this does it for me

=(IF(AND(D6="",E6="",F6=""),"",IF(E6="",SUM(D6*F6),IF(F6="",SUM(D6*E6),SUM(D6*F6)))))+(IF(AND(H6="",I6="",J6=""),"",IF(I6="",SUM(H6*J6),IF(J6="",SUM(H6*I6),SUM(H6*J6)))))+(IF(AND(L6="",M6="",N6=""),"",IF(M6="",SUM(L6*N6),IF(N6="",SUM(L6*M6),SUM(L6*N6)))))+(IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6)))))

If not, let me know and I'll crack it and try to save your hair!

 

[OTSD1983]

Hi Chrismacldn, that's brilliant thank you! The formula above is what I was trying to work out for hours, I don't usually use Excel like this anymore! I just couldn't work out how to stack the calculation to the other 3 'operations' and like an idiot was putting ,IF instead of +IF - small mistake thats left me with a red face, but at least enough hair for a combover

I messed up the formula I left in the sheet which may have confused you into what I was trying to do, sorry! I wanted both third and fourth columns (if populated) to multiply the preceeding column, wheras I notice the code you posted didnt stack the two column multiplications (except in the 4th operation), so the actual formula to get it to work is:

=(IF(AND(D6="",E6="",F6=""),"",IF(E6="",SUM(D6*F6),IF(F6="",SUM(D6*E6),SUM(D6*E6*F6)))))+(IF(AND(H6="",I6="",J6=""),"",IF(I6="",SUM(H6*J6),IF(J6="",SUM(H6*I6),SUM(H6*I6*J6)))))+(IF(AND(L6="",M6="",N6=""),"",IF(M6="",SUM(L6*N6),IF(N6="",SUM(L6*M6),SUM(L6*M6*N6)))))+(IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6)))))

If I had ever worked this out it would have been a long time coming so thanks a lot for your help, it's greatly appreciated

 

[chrismacldn]

Hi Chrismacldn, that's brilliant thank you! The formula above is what I was trying to work out for hours, I don't usually use Excel like this anymore! I just couldn't work out how to stack the calculation to the other 3 'operations' and like an idiot was putting ,IF instead of +IF - small mistake thats left me with a red face, but at least enough hair for a combover

I messed up the formula I left in the sheet which may have confused you into what I was trying to do, sorry! I wanted both third and fourth columns (if populated) to multiply the preceeding column, wheras I notice the code you posted didnt stack the two column multiplications (except in the 4th operation), so the actual formula to get it to work is:

If I had ever worked this out it would have been a long time coming so thanks a lot for your help, it's greatly appreciated

No problem.

the above code gives an error if an operation is not filled in, I have amended this so it allow for only some operations to be filled in.

Updated code:

=IF((IF(AND(D6="",E6="",F6=""),"",IF(E6="",SUM(D6*F6),IF(F6="",SUM(D6*E6),SUM(D6*E6*F6))))) = "",0, (IF(AND(D6="",E6="",F6=""),"",IF(E6="",SUM(D6*F6),IF(F6="",SUM(D6*E6),SUM(D6*E6*F6))))))+IF((IF(AND(H6="",I6="",J6=""),"",IF(I6="",SUM(H6*J6),IF(J6="",SUM(H6*I6),SUM(H6*I6*J6))))) = "",0, (IF(AND(H6="",I6="",J6=""),"",IF(I6="",SUM(H6*J6),IF(J6="",SUM(H6*I6),SUM(H6*I6*J6))))))+IF((IF(AND(L6="",M6="",N6=""),"",IF(M6="",SUM(L6*N6),IF(N6="",SUM(L6*M6),SUM(L6*M6*N6))))) = "",0, (IF(AND(L6="",M6="",N6=""),"",IF(M6="",SUM(L6*N6),IF(N6="",SUM(L6*M6),SUM(L6*M6*N6))))))+IF((IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6))))) = "",0, (IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6))))))

 

[Oaktree]

Note that P6*Q6*R6 will give a 0 if Q6 or R6 is empty, but PRODUCT(P6:R6) won't.

As such, I think you can just use =PRODUCT(P6:R6) in cell S6 and replace all of those nested IFs.

Similarly, to sum all 4 operations, I believe you can just use =SUM(PRODUCT(D6:F6),PRODUCT(H6:J6),PRODUCT(L6:N6),PRODUCT(P6:R6))

 

[chrismacldn]

Updated code: (also displays blank cell is all four operations are not filled in i.e. blank line)

=IF(IF((IF(AND(D6="",E6="",F6=""),"",IF(E6="",SUM(D6*F6),IF(F6="",SUM(D6*E6),SUM(D6*E6*F6))))) = "",0, (IF(AND(D6="",E6="",F6=""),"",IF(E6="",SUM(D6*F6),IF(F6="",SUM(D6*E6),SUM(D6*E6*F6))))))+IF((IF(AND(H6="",I6="",J6=""),"",IF(I6="",SUM(H6*J6),IF(J6="",SUM(H6*I6),SUM(H6*I6*J6))))) = "",0, (IF(AND(H6="",I6="",J6=""),"",IF(I6="",SUM(H6*J6),IF(J6="",SUM(H6*I6),SUM(H6*I6*J6))))))+IF((IF(AND(L6="",M6="",N6=""),"",IF(M6="",SUM(L6*N6),IF(N6="",SUM(L6*M6),SUM(L6*M6*N6))))) = "",0, (IF(AND(L6="",M6="",N6=""),"",IF(M6="",SUM(L6*N6),IF(N6="",SUM(L6*M6),SUM(L6*M6*N6))))))+IF((IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6))))) = "",0, (IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6)))))) = 0,"",IF((IF(AND(D6="",E6="",F6=""),"",IF(E6="",SUM(D6*F6),IF(F6="",SUM(D6*E6),SUM(D6*E6*F6))))) = "",0, (IF(AND(D6="",E6="",F6=""),"",IF(E6="",SUM(D6*F6),IF(F6="",SUM(D6*E6),SUM(D6*E6*F6))))))+IF((IF(AND(H6="",I6="",J6=""),"",IF(I6="",SUM(H6*J6),IF(J6="",SUM(H6*I6),SUM(H6*I6*J6))))) = "",0, (IF(AND(H6="",I6="",J6=""),"",IF(I6="",SUM(H6*J6),IF(J6="",SUM(H6*I6),SUM(H6*I6*J6))))))+IF((IF(AND(L6="",M6="",N6=""),"",IF(M6="",SUM(L6*N6),IF(N6="",SUM(L6*M6),SUM(L6*M6*N6))))) = "",0, (IF(AND(L6="",M6="",N6=""),"",IF(M6="",SUM(L6*N6),IF(N6="",SUM(L6*M6),SUM(L6*M6*N6))))))+IF((IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6))))) = "",0, (IF(AND(P6="",Q6="",R6=""),"",IF(Q6="",SUM(P6*R6),IF(R6="",SUM(P6*Q6),SUM(P6*Q6*R6)))))))

 

[OTSD1983]

Thanks Chrismacldn, good spot! I didn't even realise!

I'm grateful you posted that link, this is getting above my comfort level

Thanks Again