aggregating (sum) selected range depending on a reference

[zaron]

Hi,
I've just registered for Mr excel forum because I need to find a way to sum a certain Range depending on the value of the date (multiple value for the same date). In other term, I want to sum every minute together and every hour together.

What I want is help with the VBA code that would help me do that and here is an example of what I want :
___A __ B __C __D
1 12:00 _3 __24 _64
2 12:00 _7 __24 _ 64
3 12:00 _14 _24 _ 64
4 12:01 _20 _40 _ 64
5 12:01 _15 _40 _64
6 12:01 _ 5 _40 _ 64


So if we consider A and B to be our Data, I would want C to be an aggregation by the minute and D an aggregation by the hour (considering I have only 2 minute in the hour).
I Know I'm not being very clear on the detail, but I hope you can help me.
By the way, I'm using Excel 2007 with Visual Basic for my macros.

Anything you can give me to achieve my goal? advice, code, existing fonction?

appreciated,
Zaron

 

[venkat1926]

Excel Workbook
	A	B	C	D	E	F
1	time	data	minute	hour	acc tomin	acc to hr
2	12:00	3	0	12	24	64
3	12:00	7	0	12	24	64
4	12:00	14	0	12	24	64
5	12:01	20	1	12	40	64
6	12:01	15	1	12	40	64
7	12:01	5	1	12	40	64
Sheet2

Excel 2003

Cell Formulas
Range	Formula
C2	=MINUTE(A2)
C3	=MINUTE(A3)
C4	=MINUTE(A4)
C5	=MINUTE(A5)
C6	=MINUTE(A6)
C7	=MINUTE(A7)
D2	=HOUR(A2)
D3	=HOUR(A3)
D4	=HOUR(A4)
D5	=HOUR(A5)
D6	=HOUR(A6)
D7	=HOUR(A7)
E2	=SUMPRODUCT(($C$2:$C$7=C2)*$B$2:$B$7)
E3	=SUMPRODUCT(($C$2:$C$7=C3)*$B$2:$B$7)
E4	=SUMPRODUCT(($C$2:$C$7=C4)*$B$2:$B$7)
E5	=SUMPRODUCT(($C$2:$C$7=C5)*$B$2:$B$7)
E6	=SUMPRODUCT(($C$2:$C$7=C6)*$B$2:$B$7)
E7	=SUMPRODUCT(($C$2:$C$7=C7)*$B$2:$B$7)
F2	=SUMPRODUCT(($D$2:$D$7=D2)*($B$2:$B$7))
F3	=SUMPRODUCT(($D$2:$D$7=D3)*($B$2:$B$7))
F4	=SUMPRODUCT(($D$2:$D$7=D4)*($B$2:$B$7))
F5	=SUMPRODUCT(($D$2:$D$7=D5)*($B$2:$B$7))
F6	=SUMPRODUCT(($D$2:$D$7=D6)*($B$2:$B$7))
F7	=SUMPRODUCT(($D$2:$D$7=D7)*($B$2:$B$7))

see the data above . I have introuduced in between two columns C and D and results aein E and F. see the formulas in thse columns.
the formulas are written only in the row no. 2 and copied down. the values in C and D column are formatted as numbers without decimal

 

[Peter_SSs]

Welcome to the MrExcel board!

If I have understood correctly, I think these formulas, copied down, should do what you want. You may need to adjust the ranges if you have more than 100 rows.

Excel Workbook
	A	B	C	D
1	Time	Data	Sum by Minute	Sum by Hour
2	12:00	3	24	64
3	12:00	7	24	64
4	12:00	14	24	64
5	12:01	20	40	64
6	12:01	15	40	64
7	12:01	5	40	64
8
zaron

 

[zaron]

I thank you both, it's doing exactly what I wanted :D
I'll surely ask other question in the futur, thanks for your help