# Summing up the data at regular time intervals

#### Miramolin

##### New Member
Hello,
I have a data set that is now in two columns.
One column denotes time.
The other column denotes a cost variable.

An example of 2 rows:

Time..............................Cost
Sept 8 2014 14:23:03....3.45
Sept 8 2014 14:04:45....6.01

…and so on (It literally looks like that, please ignore the dots)

What I want is for cost to be summated at regular time intervals, whether daily, weekly or even hourly, (I need this for time series analysis (but don’t worry about that)). The number of costs varies per hour and per day so I imagine the way forward is a formula that can 'read' the info in the time column.

Guys, what is the most simple way of doing this?

Oh, and I'm using Excel for Mac, and I'm pretty new to the idea of macros!

- Thanks in advance ! : )
M

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi, how will you set said interval? will it be stated in a specific cell?

Thanks for the response cyrilbird.

I suppose I just need a function that will, for instance:

add up all the cost values which have associated time values between 14:00:00 and 15:00:00 and output the sum, presumably next to a column that states 15:00:00. The function should do the same for 15:00:00 to 16:00:00 and so on and so forth.

I expect that my understanding of excel is quite basic compared to yours, so I am unfamiliar with how to 'set an interval'. But I think I know what you mean.

I look forward to your response!
M

 A B 1 9/18/14 2:00 PM 2 9/18/14 3:00 PM =SUMIFS(*cost col*, *time col* 3 9/18/14 4:00 PM =SUMIFS(*cost col*, *time col*

<tbody>
</tbody>

If you're setting up the time intervals in such a way they are listed in a column this will work. The SUMIFS formula will find all times greater than or equal to the previous time and less than the current time and add their related cost. For example, B2 is finding all times between 9/18/14 2:00 PM and 9/18/14 3:00 PM and adding their related costs.

Last edited:
Thanks for the formula runwest,

I think the problem is that each cost value is recorded at a specific time, which makes the challenge more tricky!

For instance here is a list of the first few 'time' values

 Sept 8 2014 14:52:03 Sept 8 2014 14:47:40 Sept 8 2014 14:39:19 Sept 8 2014 14:35:33 Sept 8 2014 14:33:57 Sept 8 2014 14:22:11 Sept 8 2014 14:21:54 Sept 8 2014 14:04:37 Sept 8 2014 13:33:58 Sept 8 2014 12:56:12

<tbody>
</tbody>

What is required is a column that states between 13:00:00 and 14:00:00 the total cost was 'x'. and that needs to happen for ± 30 days, (where each time has a corresponding cost)

So I presume I need a formula that can 'read the time'... : S

Thanks for the formula runwest,

I think the problem is that each cost value is recorded at a specific time, which makes the challenge more tricky!

For instance here is a list of the first few 'time' values

 Sept 8 2014 14:52:03 Sept 8 2014 14:47:40 Sept 8 2014 14:39:19 Sept 8 2014 14:35:33 Sept 8 2014 14:33:57 Sept 8 2014 14:22:11 Sept 8 2014 14:21:54 Sept 8 2014 14:04:37 Sept 8 2014 13:33:58 Sept 8 2014 12:56:12

<tbody>
</tbody>

What is required is a column that states between 13:00:00 and 14:00:00 the total cost was 'x'. and that needs to happen for ± 30 days, (where each time has a corresponding cost)

So I presume I need a formula that can 'read the time'... : S
Given in A1:
 timecode val starts 13:00 Sep 09, 2014 12:40 8 ends 14:00 Sep 09, 2014 12:55 9 Sep 09, 2014 13:11 4 Sum: 12 Sep 09, 2014 13:27 1 Sep 09, 2014 13:43 6 Sep 09, 2014 13:59 1 Sep 09, 2014 14:15 4 Sep 09, 2014 14:30 8

<tbody>
</tbody>

Note:
cells in A2 and below are dates (9/9/2014 12:40:00 PM) formatted as mmm dd, yyyy h:mm:ss
and cells in E1 and E2 are time (1:00:00 PM) formatted as h:mm

Replies
1
Views
245
Replies
41
Views
2K
Replies
1
Views
143
Replies
5
Views
704
Replies
2
Views
569

1,214,324
Messages
6,118,890
Members
448,854
Latest member
Eduard_Stoo

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