I have a dataset that looks like this:
I want to total individual instances of the number of hours and liters. Each "hour" represents an hourly interval on my measuring device. Each "water" is the number of liters of water that flowed through the device in that hour. Thus the desired output is:
If there are missing rows of dates/hours or if there is a row where water=BLANK, we should assume there was 0 water. If there are multiple consecutive hours of water, then we assume that is one instance, so I want to total the number of hours and the amount of water that flowed. Is this possible to do with standard excel syntax? I tried various combinations of IF and SUMIF but couldn't quite figure this out.
Code:
Date Hour Water
1/1/2020 2 5
1/1/2020 3 45
1/1/2020 4 63
1/1/2020 24 35
1/2/2020 1 24I want to total individual instances of the number of hours and liters. Each "hour" represents an hourly interval on my measuring device. Each "water" is the number of liters of water that flowed through the device in that hour. Thus the desired output is:
Code:
Hours Water
3 113
2 59If there are missing rows of dates/hours or if there is a row where water=BLANK, we should assume there was 0 water. If there are multiple consecutive hours of water, then we assume that is one instance, so I want to total the number of hours and the amount of water that flowed. Is this possible to do with standard excel syntax? I tried various combinations of IF and SUMIF but couldn't quite figure this out.
