Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Extending Months (2007)

    Loungers,

    I have the following formula - where $F$1= 1 to 12 and matches this to the corresponding month of the year - ie 1 = January 2007, etc. This works fine for one year, but I now need to extend the months to include 2008. How can I modify the formula so it recognises 13 as January 2008 and so on?

    =IF(F7="",COUNTIF($C$10:$C$191,Lookup!D19),SUMPROD UCT((MONTH($B10:$B1987)=$F$1)*($C10:$C1987=7.1)))

    I hope that makes sense.

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

    Re: Extending Months (2007)

    It might be best to put the year in another cell, e.g. F2, and change the formula to:

    =IF(F7="",COUNTIF($C$10:$C$191,Lookup!D19),SUMPROD UCT((MONTH($B10:$B1987)=$F$1)*(YEAR($B10:$B1987)=$ F$2)*($C10:$C1987=7.1)))

    For December 2007, you'd enter 12 in F1 and 2007 in F2. For January 2008, you'd enter 1 in F1 and 2008 in F2.

    Or you could use this:

    =IF(F7="",COUNTIF($C$10:$C$191,Lookup!D19),SUMPROD UCT((12*(YEAR($B10:$B1987)-2007)+MONTH($B10:$B1987)=$F$1)*($C10:$C1987=7.1)))

    and enter 12 in F1 for December 2007, 13 for January 2008 etc.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Extending Months (2007)

    Hans - you continue to amaze me! - that must be some sort of response record.

    Thanks for your solutions - will plug them in a decide on the best option

    Regards

  4. #4
    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: Extending Months (2007)

    >"that must be some sort of response record."
    Not really - that was 8 minutes. I think Hans' record is probably more like 8 seconds! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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