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

Operation 1 | Operation 2 | Operation 3 | Operation 4 | |||||||||||||||

Line | Part Description | Operation Name | Hourly Rate | Machining Hours | Manual Multiplier | Operation Name | Hourly Rate | Machining Hours | Manual Multiplier | Operation Name | Hourly Rate | Machining Hours | Manual Multiplier | Operation Name | Hourly Rate | Machining Hours | Manual Multiplier | Total In-Process Cost |

1 | Boring | £77.94 | 10 | 10 | Fitting | £48.64 | 10 | 10 | Boring | £77.94 | 10 | 10 | Boring | £77.94 | 10 | 10 | £7,794 | |

2 |

<tbody>

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