Help needed with nested IF formula to total up

OTSD1983

New Member
Joined
May 12, 2016
Messages
5
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


Operation 1Operation 2Operation 3Operation 4
LinePart DescriptionOperation NameHourly RateMachining HoursManual MultiplierOperation NameHourly RateMachining HoursManual MultiplierOperation NameHourly RateMachining HoursManual MultiplierOperation NameHourly RateMachining HoursManual MultiplierTotal In-Process Cost
1Boring£77.941010Fitting£48.641010Boring£77.941010Boring£77.941010£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
Joined
May 12, 2016
Messages
5
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
Joined
Jun 20, 2002
Messages
8,001
Office Version
  1. 365
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
Joined
Sep 11, 2015
Messages
28
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)))
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top