Consulting

Results 1 to 4 of 4

Thread: Help with sumproduct function

  1. #1
    VBAX Contributor
    Joined
    Apr 2006
    Posts
    144
    Location

    Help with sumproduct function

    Hi guys,

    I need some help from formula experts and attach a small sample for your reference.

    The dates when each order is received and when it is completed and despatched are logged in column A & B respectively.

    I want to count the number of outstanding orders in each day. Any orders which are completed on same date are excluded. My sumproduct formula in column F needs to be modified to include items without a completion date in column B as well.

    Thanks in advance.

    Regards


    kp

  2. #2
    VBAX Contributor
    Joined
    Apr 2006
    Posts
    144
    Location
    Guys,

    I think I got this problem sorted. In cell F2, I have:

    =SUMPRODUCT(--(A$2:A$629<=E2),--(B$2:B$629>E2))+SUMPRODUCT(--(B$2:B$629<=E2),--(B$2:B$629=0))
    Regards


    kp

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    You could combine them

    =SUMPRODUCT(((A$2:A$629<=E2)*(B$2:B$629>E2))
    +((B$2:B$629<=E2)*(B$2:B$629=0))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Contributor
    Joined
    Apr 2006
    Posts
    144
    Location
    Wow. Never thought it's possible. Thanks Bob.

Posting Permissions

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