I have split up my data in the following manner:
A B C D E
8 8 4:30
8 9 8:10 0:00 3:25
9 9 3:05
9 9 49:45
9 9 143:20
9 9 8:00
9 9 1:45
9 9 7:35
9 9 149:15
9 9 14:55
9 10 150:55 0:00 40:25
10 10 17:15

The first two columns represent months and the next three represent
downtime. I have split up the downtime where it overlaps between months.
Column C has downtime in with respect to the same row and for the month in
column A. Column E is with respect to Column B for the same row. What I need
to do is add these three clomns together based on an observation date I enter
in a different sheet. I was trying;
Obs. date Start End

sumproduct(--(A1:A1000=month(Start)),--(A1:A1000<=month(End)),(C1:C1000))+sumproduct(--(A1:A1000=month(Start)),--(A1:A1000<=month(End)),(D1:D1000))+sumproduct(--(A1:A1000=month(Start)),--(A1:A1000<=month(End)),(E1:E1000))

but this is grabbing more colums than I want, also it is a very long formula
and I need to replicate this formula for 15 worksheets. Any help would be
great.

=SUMPRODUCT(--(A1:A1000=MONTH(start)),--(A1:A1000<=MONTH(end)),C1:C1000)
+SUMPRODUCT(--(A1:A1000=MONTH(start)),--(A1:A1000<=MONTH(end)),D1:D1000)
+SUMPRODUCT(--(B1:B1000=MONTH(start)),--(B1:B1000<=MONTH(end)),E1:E1000)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"vito" wrote in message
...
I have split up my data in the following manner:
A B C D E
8 8 4:30
8 9 8:10 0:00 3:25
9 9 3:05
9 9 49:45
9 9 143:20
9 9 8:00
9 9 1:45
9 9 7:35
9 9 149:15
9 9 14:55
9 10 150:55 0:00 40:25
10 10 17:15

The first two columns represent months and the next three represent
downtime. I have split up the downtime where it overlaps between months.
Column C has downtime in with respect to the same row and for the month in
column A. Column E is with respect to Column B for the same row. What I
need
to do is add these three clomns together based on an observation date I
enter
in a different sheet. I was trying;
Obs. date Start End

sumproduct(--(A1:A1000=month(Start)),--(A1:A1000<=month(End)),(C1:C1000))+sumproduct(--(A1:A1000=month(Start)),--(A1:A1000<=month(End)),(D1:D1000))+sumproduct(--(A1:A1000=month(Start)),--(A1:A1000<=month(End)),(E1:E1000))

but this is grabbing more colums than I want, also it is a very long
formula
and I need to replicate this formula for 15 worksheets. Any help would be
great.

Thanks worked great.

"Bob Phillips" wrote:

=SUMPRODUCT(--(A1:A1000=MONTH(start)),--(A1:A1000<=MONTH(end)),C1:C1000)
+SUMPRODUCT(--(A1:A1000=MONTH(start)),--(A1:A1000<=MONTH(end)),D1:D1000)
+SUMPRODUCT(--(B1:B1000=MONTH(start)),--(B1:B1000<=MONTH(end)),E1:E1000)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"vito" wrote in message
...
I have split up my data in the following manner:
A B C D E
8 8 4:30
8 9 8:10 0:00 3:25
9 9 3:05
9 9 49:45
9 9 143:20
9 9 8:00
9 9 1:45
9 9 7:35
9 9 149:15
9 9 14:55
9 10 150:55 0:00 40:25
10 10 17:15

The first two columns represent months and the next three represent
downtime. I have split up the downtime where it overlaps between months.
Column C has downtime in with respect to the same row and for the month in
column A. Column E is with respect to Column B for the same row. What I
need
to do is add these three clomns together based on an observation date I
enter
in a different sheet. I was trying;
Obs. date Start End

sumproduct(--(A1:A1000=month(Start)),--(A1:A1000<=month(End)),(C1:C1000))+sumproduct(--(A1:A1000=month(Start)),--(A1:A1000<=month(End)),(D1:D1000))+sumproduct(--(A1:A1000=month(Start)),--(A1:A1000<=month(End)),(E1:E1000))

but this is grabbing more colums than I want, also it is a very long
formula
and I need to replicate this formula for 15 worksheets. Any help would be
great.