Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Location
    Knoxville, TN - USA
    Posts
    27

    Unanswered: Array Formula Help

    Greetings DBForum Users,

    I need a little help with two array formulas. I would like to sum amounts based on: start/end dates, salesman, and product. Since a picture = 1K words I thought it best just to show what I need instead of trying to describe it in detail.

    Thanks In Advance...
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi

    For your AND Formula use this instead

    =SUMPRODUCT(--(A2:A7>J2)*(A2:A7<J3)*(B2:B7=G4)*(C2:C7=G6)*(D2: D7))

    and for your OR Formula you can use this(although there will be a better way), this does work

    =SUMPRODUCT(--(A2:A7>J2)*(A2:A7<J3)*(B2:B7=J4)*(D2: D7))+SUMPRODUCT(--(A2:A7>J2)*(A2:A7<J3)*(C2:C7=J6)*(D2: D7))-SUMPRODUCT(--(B2:B7=G4)*(C2:C7=G6)*(D2: D7))

    this adds the values when both cases are individully true and deletes the sum when they are both true
    (Spaces added to stop smilies)
    Hope this Helps
    David

  3. #3
    Join Date
    Mar 2003
    Location
    Knoxville, TN - USA
    Posts
    27
    Thanks DavidCoutts,

    It worked...Good Stuff!!!

    In your reply you said...."although there will be a better way". If it does not take too much time can you briefly describe the better way?

    Also, can you breakdown the "sumproduct(--" part of your code in layman terms?

    Thanks again for your time...

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    I only said there theres probably a better way i just through the answer quickly. It is a bit of a contrived function i provided manipulating the data, to provide an answer rather than providing an answer for the, ill have a think and see if i can come up with a better way. or someone else might.

    Quote Originally Posted by dejavu1313
    Also, can you breakdown the "sumproduct(--" part of your code in layman terms?
    sumproduct basically multiplies arrays together,
    (--(A2:A7>J2) basically says get an array of boolean(TRUE OR FALSE) providing on the situation, the -- basically turns this array from True to 1 and False to 0 so we basicall build up a set of equations, and it multiplies accross the rows and adds these totals of each row together, Hence the SUM of the PRODUCTS

    Hope this makes sense

    David

  5. #5
    Join Date
    Mar 2003
    Location
    Knoxville, TN - USA
    Posts
    27

    Excel Info Link

    After a quick Google search I came up with a link that talks about the "--" and other related stuff.

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Thanks again David...

Posting Permissions

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