# Rolling Up Daily Values in a Large Spreadsheet

#### damon_l

##### New Member
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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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:

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

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

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

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.

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 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 ...

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.

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

Last edited:

Replies
1
Views
269
Replies
1
Views
158
Replies
2
Views
1K
Replies
8
Views
239
Replies
1
Views
176

1,203,137
Messages
6,053,711
Members
444,681
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back