Sum amounts based on date field

  • Thread starter Thread starter MikeK
  • Start date Start date
M

MikeK

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
 
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
 
As the OP is using the MSDG web interface, he probably can't see your
answer. Apparently it's broken yet again.
--
David Biddulph

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

Pete
 
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.
 
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,MATCH(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
 
Back
Top