Sum Formula amendment

**pauldaddyadams** · 08-29-2012, 07:03 AM

Hi,
Can anyone help me with a formula please....

I have the formula working already for similar data but when I change it to reference different data it doesn’t work so I am doing something wrong.

On the attached spreadsheet in the “Calcs” sheet is where I need the formula – highlighted in red.

The formula in green currently works which picks up data from the “receivables” tab based on the user selection of Branch, Date Range and Working capital element.

I need exactly the same but instead of summing the receivables sheet it sums the inventory sheet based on the user selections Branch (doesn’t change from above), Date Range (doesn’t change from above) but also inventory element (this is linked to cell C6 on the calcs sheet)

Please note that if the user selects the branch all it must sum all branches and if the user selects the inventory element all then it must sum

Can anyone see where I am going wrong?

**romperstomper** · 08-29-2012, 07:58 AM

Shouldn't F58 be:
=SUM((Inventory!WorkingCap=$E58)*(IF(selectedRegion=1,TRUE,Inventory!Branch=VLOOKUP($C$4,$E$13:$F$28,2,FALSE)))*(IF($C$6=1,TRUE,Inventory!WorkingCap=VLOOKUP($C$6,$O$13:$P$17,2,FALSE)))*OFFSET(Inventory!Branch,0,fromMonth+F$54))

**pauldaddyadams** · 08-29-2012, 09:06 AM

Hi,

That didnt work - it just returns zero's

**romperstomper** · 08-29-2012, 09:36 AM

It'll only return a value for the Work In progress row due to the criteria you have set up.

**pauldaddyadams** · 08-29-2012, 10:26 AM

Thanks Romperstomer! I am such an idiot, I should have spotted that!!

**pauldaddyadams** · 08-29-2012, 10:34 AM

I have just noticed that it is picking up data in cells Y58:AA28 which is shouldnt be - why is this?

I have reuploaded the file, highlighted the errors in red