Rolling Up Daily Values in a Large Spreadsheet

[damon_l]

Good Day,

I have a large spreadsheet with more than 76,000 rows.

In this spreadsheet I have 3 columns, namely Date, Time and kWh.
The kWh column gives half hourly electricity consumption values based on the Date and Time interval.

I need to roll up the date and time columns into a 24 hour period so I can get daily electricity consumption figures. What I would like is have a new column with the date such as 2018/06/01 and a 2nd corresponding column giving the corresponding total kWh for that day based on the half hourly data points in the original kWh column.

My question is what formula do I need to roll up the dates into a 24 hour period and total the kWh for that period?

Thanks
Regards

 

[James006]

Hi,

It seems to me the easiest solution would be to insert a Pivot Table and use the Group feature ...

The Pivot table will generate all your results in less than 2 minutes ... :wink:

 

[damon_l]

I need daily kWh figures with the corrosponding date but I need to manipulate the data further afterwards to run some regression models.

 

[Repush]

put the date in E2
=SUMPRODUCT(--(A2:A76000=E2),A2:A76000)

 

[Repush]

sorry . .
=SUMPRODUCT(--(A2:A76000=E2),C2:C76000)

 

[damon_l]

sorry . .
=SUMPRODUCT(--(A2:A76000=E2),C2:C76000)

Column B is the date then column C is the time in half hourly intervals starting at 00:30, then column D is the kWh data.
I tried the above but doesn't seem to work. Need to tally up the half hourly kWh data from 00:30 to 00:00 for each day and I have a few years of data to go through.

 

[Repush]

So if you want the kWh-sum per day (the time is irrelevant) the formula is
=SUMPRODUCT(--(B2:B76000=E2),D2:D76000)
In E2 is your first date, i.e. 1-1-2019, E3 = 2-1-2019 and so on

NB "Doesn't seem to work": what goes wrong?

 

[James006]

I need daily kWh figures with the corresponding date but I need to manipulate the data further afterwards to run some regression models.

In my opinion ... there is no contradiction between the two aspects ...

 

[damon_l]

So if you want the kWh-sum per day (the time is irrelevant) the formula is
=SUMPRODUCT(--(B2:B76000=E2),D2:D76000)
In E2 is your first date, i.e. 1-1-2019, E3 = 2-1-2019 and so on

NB "Doesn't seem to work": what goes wrong?

I get the result 00:00:00, a time value it seems.
Instead of a kWh value.

 

[Repush]

set format to general, there probably a whole number (so time = 00:00)
but if it's still zero post a few lines of your data here