# Sum Formula amendment

1. ## Sum Formula amendment

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?  Register To Reply

2. ## Re: Sum Formula amendment

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))  Register To Reply

3. ## Re: Sum Formula amendment

Hi,

That didnt work - it just returns zero's  Register To Reply

4. ## Re: Sum Formula amendment

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

5. ## Re: Sum Formula amendment

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

6. ## Re: Sum Formula amendment

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  Register To Reply

7. ## Re: Sum Formula amendment  Register To Reply