compare date to various date ranges and sum value
 

Hi

I am struggling to find a formula that will work with various date ranges
(that overlap) within a worksheet and return a total value for each date.

E.g.

Table 1

Start Date End Date Value per day in period (start date to end
date)
23/06/2006 22/09/2006 3
23/06/2006 22/09/2006 -39
23/08/2006 26/09/2006 255

I want to return a result that shows the total value for each day across the
entire range of days in a new column

such as

Table 2

Day Total value
23/06/2006 -36 (calculated by -39+3)
24/06/2006 -36
.....
23/08/2006 219 (calculated by 255-39+3)
24/08/2006 219
25/09/2006 255

I will be producing the column "day" in table 2 based on the earliest day
and last day in the range from table 1. I need the formula to compare each
day in table 2 against the date ranges in table 1 and calculate the total
value applicable to each day.

Essentially I am looking for the formula to generate the total value per day
for every day across the entire range of dates.

Any help much appreciated.

Thanks

compare date to various date ranges and sum value
 

Hi Al

Try
entering on Sheet2 cell A2
=SUMPRODUCT((A2=Sheet1!$A$2:$A$4)*
(A2<=Sheet2!$B$2:$B$4)*Sheet2!$C$2:$C$4)
and copy down

This will give you the revenue for each day.
--
Regards

Roger Govier


"Al" wrote in message
...
Hi

I am struggling to find a formula that will work with various date
ranges
(that overlap) within a worksheet and return a total value for each
date.

E.g.

Table 1

Start Date End Date Value per day in period (start date to
end
date)
23/06/2006 22/09/2006 3
23/06/2006 22/09/2006 -39
23/08/2006 26/09/2006 255

I want to return a result that shows the total value for each day
across the
entire range of days in a new column

such as

Table 2

Day Total value
23/06/2006 -36 (calculated by -39+3)
24/06/2006 -36
....
23/08/2006 219 (calculated by 255-39+3)
24/08/2006 219
25/09/2006 255

I will be producing the column "day" in table 2 based on the earliest
day
and last day in the range from table 1. I need the formula to compare
each
day in table 2 against the date ranges in table 1 and calculate the
total
value applicable to each day.

Essentially I am looking for the formula to generate the total value
per day
for every day across the entire range of dates.

Any help much appreciated.

Thanks

compare date to various date ranges and sum value
 

Roger

Many thanks - this works beautifully - you have saved me literally hours!

Al

"Roger Govier" wrote:

Hi Al

Try
entering on Sheet2 cell A2
=SUMPRODUCT((A2=Sheet1!$A$2:$A$4)*
(A2<=Sheet2!$B$2:$B$4)*Sheet2!$C$2:$C$4)
and copy down

This will give you the revenue for each day.
--
Regards

Roger Govier


"Al" wrote in message
...
Hi

I am struggling to find a formula that will work with various date
ranges
(that overlap) within a worksheet and return a total value for each
date.

E.g.

Table 1

Start Date End Date Value per day in period (start date to
end
date)
23/06/2006 22/09/2006 3
23/06/2006 22/09/2006 -39
23/08/2006 26/09/2006 255

I want to return a result that shows the total value for each day
across the
entire range of days in a new column

such as

Table 2

Day Total value
23/06/2006 -36 (calculated by -39+3)
24/06/2006 -36
....
23/08/2006 219 (calculated by 255-39+3)
24/08/2006 219
25/09/2006 255

I will be producing the column "day" in table 2 based on the earliest
day
and last day in the range from table 1. I need the formula to compare
each
day in table 2 against the date ranges in table 1 and calculate the
total
value applicable to each day.

Essentially I am looking for the formula to generate the total value
per day
for every day across the entire range of dates.

Any help much appreciated.

Thanks