OaklandJim
Well-known Member
- Joined
- Nov 29, 2018
- Messages
- 833
- Office Version
- 365
- Platform
- Windows
Trying to use multiple criteria in SumIfs on a series of 8760 hourly data including time-of-day (SeriesTimes) and values to sum (SeriesScaledValues). Works well, sort of.
For two criteria in Sumifs I use values in two cells whose values are 1. start time (rrStartTime1) and 2. end time (rrEndTime1). (FYI rr indicates relative row addressing.)
I looked, the serial time values are correct in those two cells.
If I use both CELL values for criteria it works. If I use two EXCEL TIME FUNCTION generated values it works. But, sometimes I also need to put in a hardwired start or end time. But, of course I cannot figger out how to do that.
Funcs 1 & 2 work, #3 does not.
=SUMIFS(SeriesScaledValues,SeriesTimes,">="&rrStartTime1,SeriesTimes,"<=" & rrEndTime1)
=SUMIFS(SeriesScaledValues, SeriesTimes,">=" & TIME(1,0,0),SeriesTimes,"<"& TIME(5,0,0))
=SUMIFS(SeriesScaledValues, SeriesTimes,">=" & rrStartTime1, SeriesTimes, "<"& TIME(1,0,0))
For two criteria in Sumifs I use values in two cells whose values are 1. start time (rrStartTime1) and 2. end time (rrEndTime1). (FYI rr indicates relative row addressing.)
I looked, the serial time values are correct in those two cells.
If I use both CELL values for criteria it works. If I use two EXCEL TIME FUNCTION generated values it works. But, sometimes I also need to put in a hardwired start or end time. But, of course I cannot figger out how to do that.
Funcs 1 & 2 work, #3 does not.
=SUMIFS(SeriesScaledValues,SeriesTimes,">="&rrStartTime1,SeriesTimes,"<=" & rrEndTime1)
=SUMIFS(SeriesScaledValues, SeriesTimes,">=" & TIME(1,0,0),SeriesTimes,"<"& TIME(5,0,0))
=SUMIFS(SeriesScaledValues, SeriesTimes,">=" & rrStartTime1, SeriesTimes, "<"& TIME(1,0,0))