Sum amounts based on date field
 

I have two columns. Column A is a date time column. Column B is an amount
column. I want to calculate the amounts by the hour and place the hourly
totals in Column C and the date time of that hour in Column D. Below is an
example of the data and how I would like Column C and Column D to look.

Column A Column B Column C Column D
DATE AMOUNT TOTALS/HR DATE
01/01/2009 01:10:10 50 75
01/01/2009 01:00:00
01/01/2009 01:20:05 25 10
01/01/2009 02:00:00
01/01/2009 02:01:00 10 45
01/01/2009 03:00:00
01/01/2009 03:05:25 15 20
01/02/2009 05:00:00
01/01/2009 03:07:59 30
01/02/2009 05:05:01 20

I posted this earlier but couldn't find my post. I guess I did it
incorrectly. Any help would be appreciated. If the alignment is skewed
below is the information per column.
Column A
DATE
01/01/2009 01:10:10
01/01/2009 01:20:05
01/01/2009 02:01:00
01/01/2009 03:05:25
01/01/2009 03:07:59
01/02/2009 05:05:01

Column B
AMOUNT
50
25
10
15
30
20

Column C
TOTALS/HR
75
10
45
20

Column D
DATE
01/01/2009 01:00:00
01/01/2009 02:00:00
01/01/2009 03:00:00
01/02/2009 05:00:00

Sum amounts based on date field
 

Put this in D2:

=FLOOR(MIN(A:A),1/24)

and this in D3:

=D2+1/24

Then copy D3 down as far as you need to - you will have 1-hour
increments.

Then you can put this formula in C2:

=SUMPRODUCT((A$2:A$7=D2)*(A$2:A$7<D3),B$2:B$7)

You might need to make the ranges larger in your real data, and then
you can copy this down to one cell less than the cells used in column
D (to get the time range).

Hope this helps.

Pete


On Dec 8, 10:01*pm, MikeK wrote:
I have two columns. *Column A is a date time column. *Column B is an amount
column. *I want to calculate the amounts by the hour and place the hourly
totals in Column C and the date time of that hour in Column D. *Below is an
example of the data and how I would like Column C and Column D to look.

Column A * * * * * * * * * Column B * * * * Column C * * * * * *Column D
DATE * * * * * * * * * * * *AMOUNT * * * *TOTALS/HR * * * * * *DATE
01/01/2009 01:10:10 * * * *50 * * * * * * * * * * 75 * * * * * * *
01/01/2009 01:00:00
01/01/2009 01:20:05 * * * *25 * * * * * * * * * * 10 * * * * * * *
01/01/2009 02:00:00
01/01/2009 02:01:00 * * * *10 * * * * * * * * * * 45 * * * * * * *
01/01/2009 03:00:00
01/01/2009 03:05:25 * * * *15 * * * * * * * * * * 20 * * * * * * *
01/02/2009 05:00:00
01/01/2009 03:07:59 * * * *30
01/02/2009 05:05:01 * * * *20

I posted this earlier but couldn't find my post. *I guess I did it
incorrectly. *Any help would be appreciated. *If the alignment is skewed
below is the information per column.
Column A * * * * * *
DATE * * * * * * * *
01/01/2009 01:10:10
01/01/2009 01:20:05
01/01/2009 02:01:00
01/01/2009 03:05:25
01/01/2009 03:07:59
01/02/2009 05:05:01

Column B *
AMOUNT * *
50 * * * *
25 * * * *
10 * * * *
15 * * * *
30
20

Column C *
TOTALS/HR
*75 *
*10 *
*45 *
*20 *

Column D
DATE
01/01/2009 01:00:00
01/01/2009 02:00:00
01/01/2009 03:00:00
01/02/2009 05:00:00

Sum amounts based on date field
 

I'm sorry, I forget to include this is Excel 2003. Thanks for any help.


"MikeK" wrote:

I have two columns. Column A is a date time column. Column B is an amount
column. I want to calculate the amounts by the hour and place the hourly
totals in Column C and the date time of that hour in Column D. Below is an
example of the data and how I would like Column C and Column D to look.

Column A Column B Column C Column D
DATE AMOUNT TOTALS/HR DATE
01/01/2009 01:10:10 50 75
01/01/2009 01:00:00
01/01/2009 01:20:05 25 10
01/01/2009 02:00:00
01/01/2009 02:01:00 10 45
01/01/2009 03:00:00
01/01/2009 03:05:25 15 20
01/02/2009 05:00:00
01/01/2009 03:07:59 30
01/02/2009 05:05:01 20

I posted this earlier but couldn't find my post. I guess I did it
incorrectly. Any help would be appreciated. If the alignment is skewed
below is the information per column.
Column A
DATE
01/01/2009 01:10:10
01/01/2009 01:20:05
01/01/2009 02:01:00
01/01/2009 03:05:25
01/01/2009 03:07:59
01/02/2009 05:05:01

Column B
AMOUNT
50
25
10
15
30
20

Column C
TOTALS/HR
75
10
45
20

Column D
DATE
01/01/2009 01:00:00
01/01/2009 02:00:00
01/01/2009 03:00:00
01/02/2009 05:00:00

Sum amounts based on date field
 

Did my solution not work for you? (Or can you not see my post?)

Pete

On Dec 9, 2:46*pm, MikeK wrote:
I'm sorry, I forget to include this is Excel 2003. *Thanks for any help..



"MikeK" wrote:
I have two columns. *Column A is a date time column. *Column B is an amount
column. *I want to calculate the amounts by the hour and place the hourly
totals in Column C and the date time of that hour in Column D. *Below is an
example of the data and how I would like Column C and Column D to look.

Column A * * * * * * * * * Column B * * * * Column C * * * * * *Column D
DATE * * * * * * * * * * * *AMOUNT * * * *TOTALS/HR * * * * * *DATE
01/01/2009 01:10:10 * * * *50 * * * * * * * * * * 75 * * * * * * *
01/01/2009 01:00:00
01/01/2009 01:20:05 * * * *25 * * * * * * * * * * 10 * * * * * * *
01/01/2009 02:00:00
01/01/2009 02:01:00 * * * *10 * * * * * * * * * * 45 * * * * * * *
01/01/2009 03:00:00
01/01/2009 03:05:25 * * * *15 * * * * * * * * * * 20 * * * * * * *
01/02/2009 05:00:00
01/01/2009 03:07:59 * * * *30
01/02/2009 05:05:01 * * * *20

I posted this earlier but couldn't find my post. *I guess I did it
incorrectly. *Any help would be appreciated. *If the alignment is skewed
below is the information per column.
Column A * * * * * *
DATE * * * * * * * *
01/01/2009 01:10:10
01/01/2009 01:20:05
01/01/2009 02:01:00
01/01/2009 03:05:25
01/01/2009 03:07:59
01/02/2009 05:05:01

Column B *
AMOUNT * *
50 * * * *
25 * * * *
10 * * * *
15 * * * *
30
20

Column C *
TOTALS/HR
*75 *
*10 *
*45 *
*20 *

Column D
DATE
01/01/2009 01:00:00
01/01/2009 02:00:00
01/01/2009 03:00:00
01/02/2009 05:00:00- Hide quoted text -

- Show quoted text -

Sum amounts based on date field
 

As the OP is using the MSDG web interface, he probably can't see your
answer. Apparently it's broken yet again.
--
David Biddulph

"Pete_UK" wrote in message
...
Did my solution not work for you? (Or can you not see my post?)

Pete

On Dec 9, 2:46 pm, MikeK wrote:
I'm sorry, I forget to include this is Excel 2003. Thanks for any help.



"MikeK" wrote:
I have two columns. Column A is a date time column. Column B is an
amount
column. I want to calculate the amounts by the hour and place the hourly
totals in Column C and the date time of that hour in Column D. Below is
an
example of the data and how I would like Column C and Column D to look.

Column A Column B Column C Column D
DATE AMOUNT TOTALS/HR DATE
01/01/2009 01:10:10 50 75
01/01/2009 01:00:00
01/01/2009 01:20:05 25 10
01/01/2009 02:00:00
01/01/2009 02:01:00 10 45
01/01/2009 03:00:00
01/01/2009 03:05:25 15 20
01/02/2009 05:00:00
01/01/2009 03:07:59 30
01/02/2009 05:05:01 20

I posted this earlier but couldn't find my post. I guess I did it
incorrectly. Any help would be appreciated. If the alignment is skewed
below is the information per column.
Column A
DATE
01/01/2009 01:10:10
01/01/2009 01:20:05
01/01/2009 02:01:00
01/01/2009 03:05:25
01/01/2009 03:07:59
01/02/2009 05:05:01

Column B
AMOUNT
50
25
10
15
30
20

Column C
TOTALS/HR
75
10
45
20

Column D
DATE
01/01/2009 01:00:00
01/01/2009 02:00:00
01/01/2009 03:00:00
01/02/2009 05:00:00- Hide quoted text -

- Show quoted text -

Sum amounts based on date field
 

Pete, thanks for your help. The floor function worked nicely. The problem
now is I don't have values for each hour of the day. I only want to populate
Column C and D if the hour exists in column A. I hope this makes sense.

"Pete_UK" wrote:

Put this in D2:

=FLOOR(MIN(A:A),1/24)

and this in D3:

=D2+1/24

Then copy D3 down as far as you need to - you will have 1-hour
increments.

Then you can put this formula in C2:

=SUMPRODUCT((A$2:A$7=D2)*(A$2:A$7<D3),B$2:B$7)

You might need to make the ranges larger in your real data, and then
you can copy this down to one cell less than the cells used in column
D (to get the time range).

Hope this helps.

Pete


On Dec 8, 10:01 pm, MikeK wrote:
I have two columns. Column A is a date time column. Column B is an amount
column. I want to calculate the amounts by the hour and place the hourly
totals in Column C and the date time of that hour in Column D. Below is an
example of the data and how I would like Column C and Column D to look.

Column A Column B Column C Column D
DATE AMOUNT TOTALS/HR DATE
01/01/2009 01:10:10 50 75
01/01/2009 01:00:00
01/01/2009 01:20:05 25 10
01/01/2009 02:00:00
01/01/2009 02:01:00 10 45
01/01/2009 03:00:00
01/01/2009 03:05:25 15 20
01/02/2009 05:00:00
01/01/2009 03:07:59 30
01/02/2009 05:05:01 20

I posted this earlier but couldn't find my post. I guess I did it
incorrectly. Any help would be appreciated. If the alignment is skewed
below is the information per column.
Column A
DATE
01/01/2009 01:10:10
01/01/2009 01:20:05
01/01/2009 02:01:00
01/01/2009 03:05:25
01/01/2009 03:07:59
01/02/2009 05:05:01

Column B
AMOUNT
50
25
10
15
30
20

Column C
TOTALS/HR
75
10
45
20

Column D
DATE
01/01/2009 01:00:00
01/01/2009 02:00:00
01/01/2009 03:00:00
01/02/2009 05:00:00

.

Sum amounts based on date field
 

Here is how I was able to get the results I required. I'm sure there are
other ways (and probably simpler than the methods I used).
I utilized some columns that were out of sight of easy viewing. Pete I
utilized your FLOOR formula. My actual spreadsheet contained 2073 rows.
Here are the formulas I used.
Column AH2 contains: =FLOOR(MIN(A2:$A$2073),1/24)
Column AH3 contains: =FLOOR(MIN(A3:$A$2073),1/24)
I then copied AH3 down to AH2073. This gave me a list of all my times in
the format of MM/DD/YY HH:00:00. Column AH contained duplicates. To remove
the duplicates, I entered the formula below into Column D:
=IF(ISERROR(SMALL(AJ:AJ,ROW())),"",INDEX(AH:AH,MAT CH(SMALL(AJ:AJ,(ROW()-1)),AJ:AJ,0)))
This is an array formula and has to be entered with SHIFT+CNTL+ENTER. I
copied it down to D2073.
I then entered the following in Column AJ2:
=IF(AH2="","",IF(COUNTIF($AH$2:AH2,AH2)1,"",(ROW( )-1)))
This too is an array formula and has to be entered with SHIFT+CNTL+ENTER. I
copied it down to AJ2073.

The combination of the two formulas eliminated my duplicate dates in Column
D. In Column C2 I entered:
=SUM((AH2:AH2073=C2)*(B2:B2073))
This is another array formula and required to be entered with
SHIFT+CNTL+ENTER. I copied it down to C2073.

One more thing, the columns containing dates had to be preformatted to the
date format I wanted prior to putting the formulas in them.

Thanks again Pete_UK and David Biddulph.

Mike

"MikeK" wrote:

I have two columns. Column A is a date time column. Column B is an amount
column. I want to calculate the amounts by the hour and place the hourly
totals in Column C and the date time of that hour in Column D. Below is an
example of the data and how I would like Column C and Column D to look.

Column A Column B Column C Column D
DATE AMOUNT TOTALS/HR DATE
01/01/2009 01:10:10 50 75
01/01/2009 01:00:00
01/01/2009 01:20:05 25 10
01/01/2009 02:00:00
01/01/2009 02:01:00 10 45
01/01/2009 03:00:00
01/01/2009 03:05:25 15 20
01/02/2009 05:00:00
01/01/2009 03:07:59 30
01/02/2009 05:05:01 20

I posted this earlier but couldn't find my post. I guess I did it
incorrectly. Any help would be appreciated. If the alignment is skewed
below is the information per column.
Column A
DATE
01/01/2009 01:10:10
01/01/2009 01:20:05
01/01/2009 02:01:00
01/01/2009 03:05:25
01/01/2009 03:07:59
01/02/2009 05:05:01

Column B
AMOUNT
50
25
10
15
30
20

Column C
TOTALS/HR
75
10
45
20

Column D
DATE
01/01/2009 01:00:00
01/01/2009 02:00:00
01/01/2009 03:00:00
01/02/2009 05:00:00