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]

 

[OTSD1983]

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

Oaktree, that's pretty brutal, such a simple and short formula, definately above my head now!

=IF(AND(D6:R6=""),"",SUM(PRODUCT(D6:F6),PRODUCT(H6:J6),PRODUCT(L6:N6),PRODUCT(P6:R6)))

I think this is the exact functionality I was looking for!

Thanks

 

[Oaktree]

Careful with the IF part. The AND(D6:R6="") part of your formula won't work.

If all you're trying to do is suppress the zero, I'd recommend doing that with formatting instead of an IF statement. Just use a custom number format like [$£]#,##0.00;([$£]#,##0.00);

Up to the first semicolon is how you want positive numbers to look, between the first and second semicolon is how you want negative numbers to look (hence the parentheses), and after the second semicolon is how you want zero to look. By putting nothing after the second semicolon, zeroes will show as blanks.

Then, you can still use the formulas from my previous post without the need for the IF statement.

 

[chrismacldn]

Oaktree,

I totally missed PRODUCT(), good call!

OTSD1983, if you want to use the IF function to get rid of zero's use:

=IF(SUM(PRODUCT(D6:F6),PRODUCT(H6:J6),PRODUCT(L6:N6),PRODUCT(P6:R6)) = 0,"",SUM(PRODUCT(D6:F6),PRODUCT(H6:J6),PRODUCT(L6:N6),PRODUCT(P6:R6)))