Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simplifying a Formula (2003)

    Hi

    I attach a small speadsheet in case my explanation is not good enough.

    As an example I got the following formula in a cell
    {=SUM((date1=K9)*((code1=15)+(code1=16)+(code1=25) +(code1=35))*invtotal)}

    Only problem is that I only have a combination of 4 codes in this formula and in some cases it can go up to about 15 combinations.
    Is there a way of combining the different sets of codes together in a named range for instance to solve the problem?

    Or is there a different formula that can be used?

    Regards
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Simplifying a Formula (2003)

    <P ID="edit" class=small>(Edited by sdckapr on 19-Apr-07 09:28. Added Sumproduct formula)</P>How about the array (confirm with ctrl-shift-enter):

    =SUM((date1=K9)*(ISNUMBER(MATCH(code1,beer,0))*inv total))

    or the regular formula: (confirm with enter)
    =SUMPRODUCT((date1=K9)*(ISNUMBER(MATCH(code1,beer, 0))*invtotal))

    Steve

  3. #3
    Star Lounger
    Join Date
    Apr 2006
    Location
    Accra, Ghana
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplifying a Formula (2003)

    Steve

    The only thing I can say is THANK YOU and PERFECT.

    I really appreciate it.

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •