Sumproduct, SumIfs and CountIfs perfect AND off by just a bit?

[OaklandJim]

Still trying to use multiple criteria in SumIfs and CountIfs on a series of 8760 hourly data including time-of-day (SeriesTimes) and values to sum (SeriesScaledValues). Have made some good progress...But

I put together a workbook (link below), no code, with all offending calculations. Hopefully notes give good enuf orientation to SumIfs and CountIfs (and Sumproduct) experts.

I'll try to explain briefly.

I Have several adjacent 8760 data series (columns) including "scaled values" (to be summed), dates, times, season indicator (integer) and IsWeekend? (boolean) indicator flags. Separately are time periods specified.

I need to home in on data for a given range of dates, within a given "season" and within a given time range, separately, for weekdays and weekends.

I can get correct counts and sums results three different ways with 1) monthly, 2) whether weekday or weekend, and 3) seasonal by weekday and weekend.

But when I try to add the start and end times -- as criteria -- I get answers that are apparently erroneous. BUT, they are only off by about 4 1/2 to 5%. So close.

I suspect that it is the possible illogic I'm using to specify the times for items to select rather than anything Sumproduct or SumIfs related?

Logic: pick first hour THROUGH but not including the final hour of a time period specified with start and end time. That because these are hourly values that prevail "through the entire hour". So for 5PM to 10PM picked are five values, the ones for 5PM, 6PM, 7PM, 8PM, 9PM.

Any takers? I'd be moocho appreciative if i get a solution.

http://docs.jimeyer.net/8760DataAndBins_verMrExcel.xlsm

 

[OaklandJim]

Thanx to joeu2004 who gave me some important clues here in a previous post. Based on that I suspect that my problem has to do with how I am using (misusing)?) times in Sumproduct/SumIfs/CountIfs to make comparisons. (I abandoned a fairly good VBA solution (function) that has the same issue!) Dealing with times seems a bit tricky. Perhaps it is about precision. But I do not know how to fix it. :<

I just want to loop through one column of values and one column of time "stamps" in data to find values that fall within specific hourly time slots. Given that results are off by say 4% to 5% over 8760 data items it must be the precision problem mentioned by joeu2004.

BTW, in previous post I put link to worksheet w/o any code BUT it was .xlsm so I uploaded one with .xlxs extension.

http://docs.jimeyer.net/8760DataAndBins_verMrExcel.xlsx

 

[OaklandJim]

OK, I figgered this out. Re: handling a transition among days (e.g., 9:00 PM to 5:00 AM) recall that for such time ranges I need two calcs 1. start time to and not including midnight and midnight to end hour. Well, midnight is 0 in Excel so comparing say 11:00 PM < midnight is comparing something like 0.9583 < 0.0. So, I was not picking up those hours between start time and midnight (not including midnight). Solution was to create two names -- Eleven59PM whose RefersTo is =Time(23,59,59) and Midnight whose RefersTo is Time(24,0,0) . I use THOSE to do the >= or < tests. Seems to work well. Thanx all for checking this out.

​