So I am probably doing something simply wrong but i am having trouble finding it.
the data below does on for 30 days when 30 days are up. I am trying to count the hours of the day that a given column is greater than 900
I started here
=SUMPRODUCT(--(DAY(Sheet1!E6:E13200)=DAY(Sheet2!B5)),--(MONTH(Sheet1!E6:E13200)=MONTH(Sheet2!B5)),--(YEAR(Sheet1!E6:E13200)=YEAR(Sheet2!B5)),Sheet1!F6:F13200) this did exactly what it said, it summed the stuff for that day but thats not what i want
so then i modified it
=COUNTIFS(Sheet1!G6:G13200,">900",Sheet1!E6:E13200, "=" & DATE(YEAR(Sheet2!B5),MONTH(Sheet2!B5),DAY(Sheet2!B5)))
so sheet 1 is where the data set is. and datetime is in E5
sheet 2 i have a list of dates
1/8/20 this is in sheet 2 b5
1/9/20 this is in sheet 2 b6
Again, i am trying to count the number of times a column is greater than 900 in a given day. Any help would be great.
thanks
Here is what i have
the data below does on for 30 days when 30 days are up. I am trying to count the hours of the day that a given column is greater than 900
I started here
=SUMPRODUCT(--(DAY(Sheet1!E6:E13200)=DAY(Sheet2!B5)),--(MONTH(Sheet1!E6:E13200)=MONTH(Sheet2!B5)),--(YEAR(Sheet1!E6:E13200)=YEAR(Sheet2!B5)),Sheet1!F6:F13200) this did exactly what it said, it summed the stuff for that day but thats not what i want
so then i modified it
=COUNTIFS(Sheet1!G6:G13200,">900",Sheet1!E6:E13200, "=" & DATE(YEAR(Sheet2!B5),MONTH(Sheet2!B5),DAY(Sheet2!B5)))
so sheet 1 is where the data set is. and datetime is in E5
sheet 2 i have a list of dates
1/8/20 this is in sheet 2 b5
1/9/20 this is in sheet 2 b6
Again, i am trying to count the number of times a column is greater than 900 in a given day. Any help would be great.
thanks
Here is what i have
DateTime | RTO_RIN.TR_18A06.MEAS | RTO_RIN.TR_18B06.MEAS | RTO_L2_RIN.TR_18A06.MEAS | RTO_L2_RIN.TR_18B06.MEAS |
1/8/20 12:00:00 AM | 20 | 978 | 973 | 13 |
1/8/20 1:00:00 AM | 19 | 979 | 976 | 13 |
1/8/20 2:00:00 AM | 19 | 979 | 977 | 13 |
1/8/20 3:00:00 AM | 19 | 981 | 978 | 13 |
1/8/20 4:00:00 AM | 19 | 982 | 978 | 12 |
1/8/20 5:00:00 AM | 19 | 982 | 977 | 12 |
1/8/20 6:00:00 AM | 19 | 983 | 977 | 12 |
1/8/20 7:00:00 AM | 19 | 985 | 976 | 12 |
1/8/20 8:00:00 AM | 19 | 986 | 974 | 12 |
1/8/20 9:00:00 AM | 19 | 987 | 972 | 12 |