V
vito
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
1000))+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.
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
1000))+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.