If formula randomly not working

**3smees23** · 05-22-2012, 10:17 AM

Hi,

In my spreadsheet I have formulas highlighting equipment that has been missed, they are displayed in box L46:O54 in the take off. This is being worked out by formulas in the dependencies tab.

I have added another product in the dependencies tab row 3, and for some reason it's not calculating in box L46:O54 on the takeoff sheet like the other parts. The below formulas are in cell D3 & E3 respectively.

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Can anyone work out why row 3 (dependencies tab) is acting differently from the roves above and below it.

Any help will be greatly appreciated.

Sam

**daddylonglegs** · 05-22-2012, 10:29 AM

The COUNTIF part of your formula (in D3) is considering column B, because the description in row 3 is the same as row 2 COUNTIF returns 2 and the IF returns a blank. Either put a different (from B2) description in B3....or if you need those to be the same perhaps the COUNTIF part should be looking at column A (where the part numbers are different)

**3smees23** · 05-22-2012, 11:07 AM

Ah right, well I cant change the description (B2) as they are the same part. So I have referenced A instead:

Please Login or Register  to view this content.

....but other problems arise.

1)The prompt on the takeoff is saying i need 4x Wieland Connector Packs. I need it to display a quantity of two and in one line only.

2)since changing the reference, DL3SBL doesnt appear in the prompt box.

Any help would be great.

Cheers

Sam

**NBVC** · 05-22-2012, 11:22 AM

You are kinda' messing up the whole logic we started off with.. adding tidbits at a time, doesn't help... it makes it more confusing.. as we, on this side of the fence, aren't privvy to the whole project scope from the start.

If you leave the original formula in column D the same, and change the formula in column E to:

=SUMIF('Take-Off'!$E$46:$E$63,A2,'Take-Off'!$G$46:$G$63)-SUMIF('Take-Off'!$E$46:$E$63,B2,'Take-Off'!$G$46:$G$63)

copied down, does it work for you?

**3smees23** · 05-22-2012, 12:28 PM

Hi,

I'm sorry about any frustration this has cause. I should of had clear objectives from the start.

This formula is great, thanks

Sam

**3smees23** · 05-23-2012, 06:30 AM

Hi,

Upon playing around with this further, I have realised the below formula doesn't work properly:

Please Login or Register  to view this content.

The attached sheet shows that the formula has correctly worked out that I need 90x connector packs (shown in promp box L46:O54), however if you type 10 in cell I19 (takeoff) it takes 20 off each of the the prompt total (N46).

Any help would be great

Cheers

**NBVC** · 05-23-2012, 08:10 AM

I feel like I am playing guessing games now, because I am not sure what's coming next... but you can try amending formula to:

=SUMIF('Take-Off'!$E$46:$E$63,A2,'Take-Off'!$G$46:$G$63)-SUMIF('Take-Off'!$E$46:$E$63,B2,'Take-Off'!$G$46:$G$63)/COUNTIF(B:B,B2)