+ Reply to Thread
Results 1 to 11 of 11

Return Result based on Multiple Criteria

  1. #1
    Registered User
    Join Date
    12-16-2006
    Location
    Bangkok
    MS-Off Ver
    Excel 2003
    Posts
    98

    Return Result based on Multiple Criteria

    Hi

    I have been trying this for hours but to no avail.

    I have a table with 4 columns headers
    Name, Amount, Loc and Code

    The name may look like ABC 1, ABC 2....
    The Loc may be in US, GB...
    and the Code may be AA, BB

    I need to return a result "Y" if the sum of the amount is > 100
    and "N" if the sum of the amount < 100 based on the conditions
    of the following :if

    1) Name is the same entity, such as ABC 1 and ABC 2 and
    2) Loc is the same, US..and
    3) Code is the same

    I have attached a sample to illustrates the result

    TIA
    Attached Files Attached Files
    Last edited by franciz; 09-20-2009 at 01:36 PM.
    Cheers, Francis

    A novice still learning and sharing with others for what I know

    If your question has been answered, please mark this thread as [SOLVED]

    If you are happy with the results, please give my reputation a boost by clicking the blue scales icon in the upper right portion of the blue bar of this post.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Return Result based on Multiple Criteria

    =IF( SUMPRODUCT( ($A$2:$A$14=A2) * ($C$2:$C$14=C2) * ($D$2:$D$14=D2), $B$2:$B$14) >=100, "Y","N")

    That assumes that the names are unique, rather than that ABC 1 is the same as ABC 2. If that's not the case, then you need to be specific about what makes a match.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-16-2006
    Location
    Bangkok
    MS-Off Ver
    Excel 2003
    Posts
    98

    Re: Return Result based on Multiple Criteria

    Hi shg

    Thanks for this.
    The names ABC 1 and ABC 2 although are unique but they fall under one entity, ie ABC
    therefore the sum of ABC 1 and ABC 2 with the same Loc, eg US and the same Code eg
    AA need to give the result of "Y"

    The name can repeat in the table with the same Loc but under different Code.

    The sample I have provided is a very small representation.

    TIA

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Return Result based on Multiple Criteria

    My question was, what is the rule for determining that two entities are the same? The first three letters as in your example, or something more complex?

  5. #5
    Registered User
    Join Date
    12-16-2006
    Location
    Bangkok
    MS-Off Ver
    Excel 2003
    Posts
    98

    Re: Return Result based on Multiple Criteria

    It will be characters before a space or "-"

    thanks

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Return Result based on Multiple Criteria

    Then I'd add a column that extracts the defining name,

    =LEFT(A2, FIND(" ", SUBSTITUTE(A2 & " ","-", "") ) - 1)

    ... and apply the previous formula to that.

  7. #7
    Registered User
    Join Date
    12-16-2006
    Location
    Bangkok
    MS-Off Ver
    Excel 2003
    Posts
    98

    Re: Return Result based on Multiple Criteria

    Hi Shg

    Thanks. This works fine!

    Wondering if there any way to work in one formula instead of having a helper column?

    TIA

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Return Result based on Multiple Criteria

    Yes, but it's needlessly redundant:

    =IF( SUMPRODUCT( (LEFT($A$2:$A$14, FIND(" ", SUBSTITUTE($A$2:$A$14 & " ","-", "") ) - 1)=LEFT(A2, FIND(" ", SUBSTITUTE(A2 & " ","-", "") ) - 1)) * ($C$2:$C$14=C2) * ($D$2:$D$14=D2), $B$2:$B$14) >=100, "Y","N")

  9. #9
    Registered User
    Join Date
    12-16-2006
    Location
    Bangkok
    MS-Off Ver
    Excel 2003
    Posts
    98

    Re: Return Result based on Multiple Criteria

    Hi Shg

    Thanks!

    I wanna know of this is possible as I have been trying putting it in one formula.

    TIA
    Last edited by franciz; 09-20-2009 at 01:27 PM.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Return Result based on Multiple Criteria

    There is no helper column used in the last formula.

  11. #11
    Registered User
    Join Date
    12-16-2006
    Location
    Bangkok
    MS-Off Ver
    Excel 2003
    Posts
    98

    Re: Return Result based on Multiple Criteria

    Right...
    I can't see my post and that why I repost again.
    However, I have changed the contents of my last post.
    Apology if that gave cause a confusion.

+ 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