Calculate max sum of N values in a column

[Resod2]

Hi, I am trying to calculate the maximum sum of values within an hour in column. Because the number of values within an hour isn't constant (see my example below) I'm having a lot of trouble trying to get this to work.

As you can see in the sheet below, the times are in column A and the value associated with that time is in column B. In the example below it should output 26% as this is the maximum hour worth of values.

Also, I need it to be a formula father than any VBA. Anyone know how to solve this problem?

Thanks in advance!

Test.xlsx
	A	B	C	D	E
1	Time	Value		Max Hour	26
2	05:45	2
3	05:45	3
4	07:45	4
5	10:45	2
6	10:45	8.7
7	11:00	6.2
8	11:15	2.1
9	11:45	7
10	13:15	2.1
11	16:30	2.1
12	16:30	2
13	16:45	2
Sheet1

 

[Eric W]

Welcome to the MrExcel forum!

Try:

=MAX(SUMIFS(B2:B13,A2:A13,">="&A2:A13,A2:A13,"<="&A2:A13+1/24))

 

[Resod2]

Welcome to the MrExcel forum!

Try:

=MAX(SUMIFS(B2:B13,A2:A13,">="&A2:A13,A2:A13,"<="&A2:A13+1/24))

Thanks Eric, that works perfectly! Could you please explain what the formula is doing, I think I understand but am not sure about some of it i.e. why divide by 24?

 

[JGordon11]

Another way:

Book3
	A	B	C	D	E
1	Time	Value		Max Hour	26
2	5:45:00 AM	2
3	5:45:00 AM	3
4	7:45:00 AM	4
5	10:45:00 AM	2
6	10:45:00 AM	8.7
7	11:00:00 AM	6.2
8	11:15:00 AM	2.1
9	11:45:00 AM	7
10	1:15:00 PM	2.1
11	4:30:00 PM	2.1
12	4:30:00 PM	2
13	4:45:00 PM	2
Sheet3

Cell Formulas
Range	Formula
E1	E1	=MAX(MMULT(--(ABS(TRANSPOSE(A2:A13)-A2:A13)<=0.04),B2:B13))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

Thanks Eric, that works perfectly! Could you please explain what the formula is doing, I think I understand but am not sure about some of it i.e. why divide by 24?

Sure, in Excel a day is considered to be "1", and fractions of "1" are fractions of a day. So if "1" is one day, then 1/2 = half a day = 12 hours. So 1/24 is 1/24th of a day, or 1 hour. If you were to format your A column as General instead of a time format, you'd see a bunch of decimals.

Let's say you wanted to add up all the values where the time is between 10:00 am and 1:00 pm. The SUMIFS would look like:

=SUMIFS(B2:B13,A2:A13,">="&10/24,A2:A13,"<="&13/24)

If you wanted to add up all the values in column B where the time in column A is between the time in A2 and an hour later, it would be:

=SUMIFS(B2:B13,A2:A13,">="&A2,A2:A13,"<="&A2+1/24)

If you want to calculate the same thing for EVERY time in column A, it becomes:

=SUMIFS(B2:B13,A2:A13,">="&A2:A13,A2:A13,"<="&A2:A13+1/24)

Excel calculates the SUMIFS for every case, and keeps all the results in an internal array. Then the MAX function gets the maximum value.

Hope this helps!