# Help needed with nested IF formula to total up

#### OTSD1983

##### New Member
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.

 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>

#### OTSD1983

##### New Member
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!

Code:
``=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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### Oaktree

##### MrExcel MVP
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

##### New Member
Oaktree,

I totally missed PRODUCT(), good call!

OTSD1983, if you want to use the IF function to get rid of zero's use:
Code:
``=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)))``

Replies
1
Views
497
Replies
1
Views
400
Replies
6
Views
174
Replies
45
Views
730
Replies
2
Views
298

1,129,916
Messages
5,638,976
Members
417,063
Latest member
thematulaak

### 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.

### Which adblocker are you using?

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

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