+ Reply to Thread
Results 1 to 7 of 7

Sum Formula amendment

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,683

    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?
    Attached Files Attached Files

  2. #2
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    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))
    Remember what the dormouse said
    Feed your head

  3. #3
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,683

    Re: Sum Formula amendment

    Hi,

    That didnt work - it just returns zero's

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    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. #5
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,683

    Re: Sum Formula amendment

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

  6. #6
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,683

    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
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,683

    Re: Sum Formula amendment

    correct file now uploaded
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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