+ Reply to Thread
Results 1 to 3 of 3

Match index from multiple spreadsheets-Return sum of values

  1. #1
    Registered User
    Join Date
    11-14-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    55

    Match index from multiple spreadsheets-Return sum of values

    I have a 'Master' spreadsheet with a specific reference (Master!H12) and want to return a value from different a specific column in multiple spreadsheets (i.e 'F10'B:B,'M40'!B:B), and calculating to sum where the specific reference is duplicated.

    So far I've got the following formula but it calculates only a single instance from a single spreadsheet.

    =if(ERROR(INDEX(('F10'!F:F),MATCH(Master!H12,'F10'!B:B,0)),"")

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Match index from multiple spreadsheets-Return sum of values

    Is this solved?

    If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    11-14-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003
    Posts
    55

    Re: Match index from multiple spreadsheets-Return sum of values

    I though I had this cracked, with significant help of course.

    The formula works great for numeric values only but does not pick up text variables.
    I need the formula to also pick up text variables ie "Omit", "Excl", "Incl", "Nil" as the result

    So that if a cell has "Omit" in instead of a numerical value that will be shown.

    =IF(F4<>"",SUMPRODUCT(SUMIF(INDIRECT("'"&Trades!$A$3:$A$25&"'!A3:A500"),B4,INDIRECT("'"&Trades!$A$3:$A$25&"'!E3:E500"))),"")

+ 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