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

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))

3. ## Re: Sum Formula amendment

Hi,

That didnt work - it just returns zero's

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.

5. ## Re: Sum Formula amendment

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

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

7. ## Re: Sum Formula amendment

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1