This is a simple table and I should be able to figure this out, but....Chip
and John, where are you!!!

I posted a message earlier, but as I have gotten into the formulas more, I
have found the post doesn't fully get to the root of my problem. Here's the
scenario...I have a several tables that record budgeted sales for our
products. Each table has rows of date ranges (ex. 1/1/03 to 3/31/03) and
corresponding budgeted and actual sales. The tables continue to show date
ranges until the end of the product life cycle (anywhere from 1 to 6 years).
Example:

Beg Date End Date Unit $ Budget Units Act Units YTD Units
Over/Under
Jan 1 Mar 31 $3.50 10 8
8 (2)
Apr 1 Jun 30 $3.50 7 8
16 (1)
Jul 1 Sept 31 $4.00 3 5
21 1

What I want to do is create a summary report each month showing which
products have an end date that is in the current month (ex. an end date may
be 6/15/03, but it falls in the current end date of 6/31 above, so it would
show on the summary). I also want to show if the previous period range is
under budget in the same summary (ex. current period 6/03 in range 4/1/03 to
6/30/03 and previous period 1/1/03 to 3/31/03 is under budget 2 units, so it
would show with the end date due)

Here are my issues...

1. I have the formula to find the current period due:

{=SUM(IF((BOP!$C$29:$C$71=$I$2)*(BOP!$C$29:$C$71< =$I$3),(BOP!$F$29:$F$71),"
"))}

I2 is beginning of month and I3 is EOM and C range is End Date range.

Works, no problems...I also need to be able to show the end date along with
the value (ex. 7 items budgeted, due date 6/30/03). How can I get a formula
to return the value of the End Date that is within the current month?

2. I have an array formula that looks through the date ranges (Start Date
and End Date)
to find the range that occurs in this period (current month) and then looks
to the previous period to get the variance. Here's a sample formula:

{=SUM(IF(($I$2=BOP!$B$29:$B$71)*($I$3<=BOP!$C$29: $C$71),OFFSET(BOP!$J$29:$J
$71,-1,0),""))}

The problem is it works great if the date ranges start at the beginning of a
month, but does not work if the start date is anywhere else through the
month (say one period ends May 1st and the next period begins May 2). I
can't figure out how to tell it to look at both ranges of values to see if
it falls in the current month.

3. There are instances where pricing changes may occur mid-period, so I
have 2 duplicate date ranges, with different pricing for each. For the
variance part, my formula will add the two previous periods from the last
current date range instead of the one form the correct period. Using
OFFSET(Range,-2,0) will work, but I need something to trigger the event to
tell the formula there are 2 instances of the same date. I think the best
way to attack this one is create another formula in a new column to count
the instances of that date range and if 2, use the new formula. How can I
count the instance of the date occurance?
--
Remove 'spam' from email address to contact me directly