Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Sum amounts based on date field
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 6
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,856
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 6
Default 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

.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 6
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,856
Default 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 -

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,651
Default 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 -


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 6
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct based on date entered in separate field Mercdoodle Excel Discussion (Misc queries) 3 January 31st 09 02:25 AM
New date based on one date field minus minutes in another field [email protected] Excel Discussion (Misc queries) 1 December 25th 07 04:05 PM
Lookup to find the last value based on name and date field Adam Thwaites Excel Discussion (Misc queries) 4 April 4th 07 05:15 PM
sum multiple rows based on progressing date field on each row Thane Excel Worksheet Functions 0 February 2nd 06 05:07 PM
Adding amounts based on a conditional date ruleb Excel Discussion (Misc queries) 3 October 7th 05 11:25 PM


All times are GMT +1. The time now is 06:35 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement