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

    Date problem (2003)

    Hi

    I am using the following array formula which works.
    {=SUM((date1=E$2)*(code1=$C28)*units1)}
    I have dates in row 2

    How can I adjust the formula so that date1 => D$2 and date1 <E$2.

    Your help will be appreciated

    Regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date problem (2003)

    =SUM((date1>=D$2)*(date1<E$2)*(code1=$C28)*units1)

    (again as an array formula, i.e. confirm with Ctrl+Shift+Enter)

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date problem (2003)

    Either:
    {=SUM((date1>=D$2)*(date1<E$2)*(code1=$C28)*units1 )}
    or the non-array formula:
    =SUMPRODUCT((date1>=D$2)*(date1<E$2)*(code1=$C28)* units1)
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Date problem (2003)

    Thank you for both replies.

    I would never have thought of that, I was trying AND and IF AND configurations.

    Thank you again

    Regards

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date problem (2003)

    The idea behind this kind of SUM / SUMPRODUCT formulas is that you can add as many conditions as you need in the form of extra factors in the multiplication. No IF and AND needed - you didn't have IF and AND in the original formula either.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date problem (2003)

    An alternative solution is this array formula:

    =SUM(IF((date1>=$D2)*(date1<E$2)*(code1=$C28),unit s1))

    This version can be adapted for other aggregate functions: you can replace SUM with functions such as AVERAGE, MIN, MAX, MEDIAN or MODE.

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

    Re: Date problem (2003)

    Hans

    Thank you for the lesson, I did not realize that the original formula is in fact also an if formula (in disguise) since it checks for certain conditions and all that happened now is an extra condition added.

    Now that I know that I can see a lot of applications for it in my work.

    Thank you again very much.

    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
  •