how to find and sum together all the variables in a table as they relate to daylight hours and night hours

[MAP77]

I have a csv file of consumption data recorded every 30 mins for 365 days. How would I write a formula to find all the variables that fall between say 6am and 6pm (daylight hours) and 6.30pm to 5.30am (night time hours)? Ideally I would like a total of consumption during daylight hours and night time hours for each month of the year. The file contains a column for the date, time and consumption recorded per 30 mins. I have only limited experience with basic formulas in excel so please speak english.

 

[arthurbr]

A Pivot Table seems the easiest way
You'll find tutorials at https://www.contextures.com/CreatePivotTable.html
Also search that site for " grouping" in Pivot Tables

 

[Tim_Excel_]

I have little experience with time values so it took me a while, but here you go:

=SUMPRODUCT(IF(TIMEVALUE("18:00:00")>B2:B9,IF(B2:B9>TIMEVALUE("6:00:00"),1,0),0)*C2:C9)

Make sure you edit the references and time ranges to your preferences. Furthermore, since this is an array function use ctrl shift enter instead of enter when using the formula.

This formula is derived from the thread in this link: https://superuser.com/questions/297984/how-can-i-sum-values-within-a-specific-time-and-date-range
In English:
The formula first checks if the times in your times column are before a certain time, in this case 6PM. If this is not the case, the IF statement returns "0". If this IS the case, the IF statement checks if the times are after a certain time, in this case 6AM. If this is the case, "1" is returned and otherwise "0". Finally, these 1s and 0s are multiplied by the cells in your consumption column by the SUMPRODUCT statement and these values are then summed.

 

[steve the fish]

You could use a sumifs:

=SUMIFS(C:C,A:A,">="&DATE(2018,1,1),A:A,"<"&EDATE(DATE(2018,1,1),1),B:B,">="&TIME(6,0,0),B:B,"<="&TIME(18,0,0))

where the the date is in A, the time in B and consumption is in C. This would calc for January 2018.