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

    Conditional Sum formula (Excel 2003)

    Hello everybody

    First of all, I wish everybody a Prosperous New Year.

    My problem: I want to construct a Formula that change depending on the Date in a cell.
    Say I have data in columns C to M (Dec 06 to Oct07) and have my Sum formula in column N.
    In cell N15 I have a date and I want the formula in column N to change depending on the date filled in in cell N15.

    I attach an example with more notes. Any ideas how this can be done?

    Regards

  2. #2
    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: Conditional Sum formula (Excel 2003)

    Hi,
    Enter this in N9 and copy down:

    <code>=SUMPRODUCT(($C$7:$M$7>$N$15)*C9:M9)</code>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Conditional Sum formula (Excel 2003)

    Enter the following formula in N9
    <code>
    =SUM(OFFSET(D9,0,DATEDIF(C7,$N$15,"m"),1,10-DATEDIF(C7,$N$15,"m")))
    </code>
    and fill down to N12.

    Note: this formula doesn't perform any error checking - if you enter a date before December 1, 2006 or after September 30, 2007 it will result in an error value.

    Added: Rory's formula is much better!

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

    Re: Conditional Sum formula (Excel 2003)

    Rory & Hans

    Thank you very much, this make things much easier.

    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
  •