Multiple row comparison to sum potentially multiple values.

[MHG]

Here's something like the data I have.

Start Date End Date Flow rate
1/1/08 1/10/08 10
12/13/08 12/30/08 -15
5/15/09 5/17/09 2
...... ..... ...

This list represents a running list of flow rates over varying time periods.
These times might overlap, start at the same time, end at the same time, or
not overlap at all. What I ultimately am trying to do is see how many gallons
are flowing each day for a number of years. I have a separate column that
lists each day, and here's the logic I'm looking for.

Does (date here) fall on or between the dates listed in the other table ?(ie
is there some sort of flow happening on this day?) If so then in every
instance...what is the total flow rate?

VLOOKUP would be fine, except I don't know how to get it to recognize I want
the sum of all places where this date appears. Also this list could get
long...I don't want to have a million IF(___<X<___,___)+IF(.... statements.
Is there some sort of function I can use? Ultimately I want to be able to add
to the table above a new time period where water is flowing, and see the days
that this is affects respond in the graph.

Help please!

 

[pdberger]

This appears to work:

A B C ... F G
1 Start End Flow Date Ttl Flow
2 1/1 1/10 10 1/1 Eq. Below
3 1/1 1/25 -15 1/2
4 1/15 1/31 2 1/3
5 ... ...

The formula in G2 is:
=SUMPRODUCT($C$2:$C$4,--(F2>=$A$2:$A$4),--(F2<=$B$2:$B$4))

You'd want to adjust the A2:A4, B2:B4, and C2:C4 ranges to suit your data.
HTH

 

[Herbert Seidenberg]

Excel 2007 PivotTable, PivotChart
Dynamic data entry and chart update.
With time period bars and flow columns.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_30_09.xlsm