1. ## 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. ## 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. ## 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. ## 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>

#### Posting Permissions

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