Sorry about 2 & 3. "I" corresponds to the beginning and ending dates of the
current month and "J" is the Over/Under Budget Column.

I'll work on these formulas and see what they do. Thanks for the help on
this! I have been beating my head against a wall trying to figure this one
out.


"Jerry W. Lewis" wrote in message
...
1. Use the array formula
{=MAX(IF((BOP!$C$29:$C$71=$I$2)*(BOP!$C$29:$C$71< =$I$3),(BOP!$C$29:$C$71),"
"))}

Also, you can simplify your original formula to
=SUMPRODUCT((BOP!$C$29:$C$71=$I$2)*(BOP!$C$29:$C$ 71<=$I$3),(BOP!$F$29:$F$71
))
which does not have to be array entered.

2 & 3. Your description is inadequate. Assuming that the data in your
"Example" table is in columns BOP!B to BOP!H, you still have not
indicated what is in column BOP!J.

Jerry

Karl Burrows wrote:

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?