Summing Energy Consumption for Day/Nights With Adjustable Times

[Kraken2345]

Hi, this is my first time posting here and I'm definitely not an advanced excel user but was hoping for some guidance. I'm looking at my home energy consumption to evaluate the viability of different energy technologies (solar for example). This data is just an example of a year's energy consumption I sourced from some online energy tutorial.

I've got rows of energy data for a year period, so 365 days (and rows). Each day has 24 data points (columns), which represents energy consumption per hour. I've added another column to identify the weekdays and weekends, using the WEEKDAY function.

I now want to identify total energy consumption in the day and night of weekdays and weekends, based on criteria which I can change. For example, setting daytime hours between 06:00 and 18:00, then calculating the energy consumption on weekdays and then on weekends during these hours. Also calculating the energy consumption during the weekday and weekend evenings.

Thanks for any help/advice!

 

[steve the fish]

Something like this would work for daytimes in the week:

=SUMPRODUCT((A1:X1>=TIME(6,0,0))*(A1:X1<=TIME(18,0,0))*(Y2:Y20<=5)*A2:X20)

 

[Kraken2345]

Something like this would work for daytimes in the week:

=SUMPRODUCT((A1:X1>=TIME(6,0,0))*(A1:X1<=TIME(18,0,0))*(Y2:Y20<=5)*A2:X20)

Thanks for your reply, could you explain what exactly that is doing? It appears to sum any value greater than or equal to 06:00 and less than or equal to 18:00 as long as the day value is a weekday, correct? I've put this together myself and it works for a single row but I'm struggling to get it to work for multiple rows. Also I haven't integrated the ability to differentiate a weekday from a weekend yet.

=SUMIFS(B2:Y2,B1:Y1,">="&C11,B1:Y1,"<="&C13)

Where C11 is 06:00 in a box and C13 is 18:00, this would allow it to be changed to 7am-7pm, for example.

 

[steve the fish]

It creates two arrays for your times and multiplies them together to get an array of 1s and 0s for time being within the limits. It then creates an array for the weekdays, again 1s and 0s. It then multiplies them arrays by your data. Obviously 1 times x is still x and 0 times x is 0. So once it has done all its multiplication it sums all the values. You can see what its doing if you use the evaluate formula tool on the 'formulas' menu tab at the top.

 

[steve the fish]

And yes you can substitute my time formulas in the formula for cell refs if required.

 

[Kraken2345]

That's excellent, thanks for your help I'll spend some time messing around with it now and see what I can learn.