+ Reply to Thread
Results 1 to 4 of 4

need help in sum up multi criterias (index & match)

  1. #1
    Registered User
    Join Date
    10-14-2006
    Posts
    3

    need help in sum up multi criterias (index & match)

    Hi all,

    Please find the screenshot in attached file.

    I have 4 customer in the list. Each customer can have more than 1 security number.

    as per example in the file, for customer 2, security number are same. so i will look at the security type, if the security type is different, then i will sum up both security amount. if the security type is same, then i will take the highest security amount. if the security amount is same, i will oni take 1 amount.

    how should i write the formula? i have 3000+ lines to go. can i use index & match function?

    thanks
    Attached Images Attached Images

  2. #2
    Forum Contributor
    Join Date
    04-11-2005
    Location
    London
    Posts
    259
    It depends what you want really.

    The fastest way to generate the information is inserting a pivot table. Drop Cust into the left hand pane and SecAmt into the main pane. Every time you want to refresh the data click the "!" on the pivot table toolbar.

    The other way if you want to stay closer to your current methodology is to use the SUMIF function. See graphic for example.

    HTH
    Attached Images Attached Images
    Martin Short

  3. #3
    Registered User
    Join Date
    10-14-2006
    Posts
    3
    Quote Originally Posted by MartinShort
    It depends what you want really.

    The fastest way to generate the information is inserting a pivot table. Drop Cust into the left hand pane and SecAmt into the main pane. Every time you want to refresh the data click the "!" on the pivot table toolbar.

    The other way if you want to stay closer to your current methodology is to use the SUMIF function. See graphic for example.

    HTH

    but then i dun want sum up everything, i want to check if security no. for tat customer is repeating, thne i will check whether the type is same o different. if same, i will oni take 1 security amount.

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by aroundyou
    but then i dun want sum up everything, i want to check if security no. for tat customer is repeating, thne i will check whether the type is same o different. if same, i will oni take 1 security amount.
    No idea, but perhaps the attached might trigger some brighter person to a result.

    I presume that your data is in Customer order (as shown in your .jpg) and that you want to pick up unique security amounts as shown shaded in column I (matches your indicated selections in column D)

    I have not yet worked out how to omit the yellow-flagged 184 (184,000) nor how to make the 'Indirect' dynamic within the sumproduct, I could only achieve that with 3 helper columns.

    You may just have to set this to VB code to achieve all that you asked.

    hth
    ---

    added, in column J, in J2 put:

    =IF(SUMPRODUCT(--(INDIRECT(E2)=B2)*(--(INDIRECT(F2)=C2)))>1,IF(I2="","","dup"),"")

    this will highlight the duplicates prior to the last entry (ie, the first of 2, the first 2 of 3 etc), but the Indirect ranges would need to be B-Start:B-Last etc, not B-Row-:B-last for the customer to include all duplicate entries.
    Attached Files Attached Files
    Last edited by Bryan Hessey; 10-15-2006 at 08:05 PM.

+ 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