+ Reply to Thread
Results 1 to 10 of 10

Range Name in Formula?

  1. #1
    Registered User
    Join Date
    05-29-2006
    Posts
    37

    Range Name in Formula?

    =SUMPRODUCT(('DVP Trades'!$B$2:$B$1000="LOAN")*('DVP Trades'!$C$2:$C$1000=A31)*('DVP Trades'!$A$2:$A$1000=FUNDS_SS)*('DVP Trades'!$F$2:$F$1000))

    Maybe this is impossible, someone let me know.

    I am using the above formula. I am trying to use range names where its bolded in place of repeating different cells. However my the result of the formula is N/A.

    The range name of "Funds_SS" is a group of 12 funds. What I want to do is take the total of anything that is a Loan in column B, with a given client in column A, if it occurs in any of the funds in range name FUNDS_SS, and the value of the loan is in column F. Does anyone know what I am talking about? Can it not be done? I don't know if I can explain it any better.

    Thanks in advance,

    EJ

  2. #2
    Forum Contributor WinteE's Avatar
    Join Date
    04-07-2007
    Location
    Netherlands
    Posts
    544
    Please post an example-file.
    Just keep it simple !


    http://www.excelguide.eu
    In English as well as in Dutch

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  4. #4
    Registered User
    Join Date
    05-29-2006
    Posts
    37
    Well, I have a sample attached. I hope it gets the point across because I had to change/remove a lot of client information.

    Currently under the Client_1 tab, I have SUMPRODUCT formulas like =SUMPRODUCT((Trades!$B$2:$B$10000="LOAN")*(Trades!$C$2:$C$10000=A2)*(Trades!$A$2:$A$10000=B2)*(Trades!$F$2:$F$10000)). Here all I would need to do is change the broker in column A or fund in B and the Loan and Return totals in C and D will be populated.

    What I want to do is remove the necessity of having the B column. I want to be able to change the above formula to something like...=SUMPRODUCT((Trades!$B$2:$B$10000="LOAN")*(Trades!$C$2:$C$10000=A15)*(Trades!$A$2:$A$10000=Fund_SS)*(Trades!$F$2:$F$10000)). Here I would use FUND_SS as a range name. Then the formula would pick up all activity that occurs in the "Trades" tab for any fund in the specific range name.

    My attempt is highlighted in yellow. The correct answer is the value in red.

    Not sure if this clears things up at all.

    EJ
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-29-2006
    Posts
    37
    Its ok if I can't find a solution to this. My file works I was just trying to make it more...streamlined.

    EJ

  6. #6
    Registered User
    Join Date
    05-29-2006
    Posts
    37
    A final bump before the end of the year.

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Hello EJS,

    Use this formula

    =SUMPRODUCT((Trades!$B$2:$B$10000="LOAN")*(Trades!$C$2:$C$10000=A17)*ISNUMBER(MATCH( Trades!$A$2:$A$10000,Fund_SS,0)), Trades!$F$2:$F$10000)
    Last edited by daddylonglegs; 12-31-2007 at 10:25 AM.

  8. #8
    Registered User
    Join Date
    05-29-2006
    Posts
    37
    Quote Originally Posted by daddylonglegs
    Hello EJS,

    Use this formula

    =SUMPRODUCT((Trades!$B$2:$B$10000="LOAN")*(Trades!$C$2:$C$10000=A17)*ISNUMBER(MATCH( Trades!$A$2:$A$10000,Fund_SS,0)), Trades!$F$2:$F$10000)
    I tested the formula provided and I think it works. I will have to try it throughout the rest of my spreadsheet with real examples to make sure. Can you explain to me how the ISNUMBER(MATCH( Trades!$A$2:$A$10000,Fund_SS,0)) part of the formula works? Like what is the logic so I can understand? Thanks,

    EJ

  9. #9
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Using ISNUMBER(MATCH.... is a fairly standard technique if you want to check for a match with multiple values, the range to check against has to be a single row or column, doesn't matter if values are repeated in the range to check against....

    In it's basic form the formula...

    =MATCH(Trades!$A$2,Fund_SS,0)

    returns either #N/A, if Trades!$A$2 doesn't match any value in Fund_SS, or a number, indicating which row of Fund_SS has the first match, so when you add ISNUMBER this gives TRUE when Trades!$A$2 matches any value in Fund_SS, FALSE if there is no match.

    Of course, for sumproduct, you want to test the whole range so you use the whole range Trades!$A$2:$A$10000

    see example 1 here for something similar

  10. #10
    Registered User
    Join Date
    05-29-2006
    Posts
    37
    Thank you very much for your help. I applied a variation of the formula you provided to my whole spreadsheet and it works perfectly.

    Thank you again. I appreciate it.

    EJ

+ 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