Function that identifies specific time values and then sums adjacent cells

[zeegerman]

Good Morning Everybody,

It's that time again when I need your help please. Here's an overview of the challenge:


1. A standard worksheet where, in column A, there are 60 time entries from cells A2 to A61.

2. Each time entry contains the hour and minute values (i.e., 11:08, 11:09, 11:10 etc.)

3. Next to each of the cells in column A, there is number data only in column B. There are no blank cells or errors in any of the cells.


What I would like to be able to do is as follows:

a) At intervals where the times in column A are exactly divisible by 5 (i.e., 11:05, 11:10, 11:15 etc.), I need the function to sum the previous 5 entries in column B. For example:

Cell# Column A Column B
A7 11:06 56
A8 11:07 200
A9 11:08 314
A10 11:09 -41
A11 11:10 78

b) The function should then automatically calculate the sum total as =SUM(B7:B11) as it should recognise that cell A11 contains a time value divisible by 5.

c) The function should be inserted into cell B70 and the sum value should then appear in cell B70.

Many thanks in advance for your help. I haven't yet attached a template but am happy to do so if it helps.

Kindest regards,

[Glenn Kennedy]

Hi there. Is this what you had in mind?

[samba_ravi]

Please attach sample file with expected result
What your expected result in B

[zeegerman]

Hi Glenn,

Sorry for my delayed response, I have just downloaded the file.

Thanks very much!

The only detail I neglected to mention is that the time values are part of a DDE data feed, and they populate cells A2 to A61 in continuous rotation (i.e., data feeds in starting at A2, and when cell A61 is populated, all other new entries push the data up, erasing the older values in cell A2).

This means that the time values divisible by 5 are also continuously pushed up, and I would like to be able to track the adjacent sum values accordingly. Is there a way of amending the function?

Thanks again for all your help!

[samba_ravi]

please attach sample excel file with expected result

[Glenn Kennedy]

That should be OK. I'm away right now; so look back a little later.

[bartelba]

Hi,

Try this one.

Bas

[Glenn Kennedy]

OK. I had tried to take a shortcut... and you caught me out!!. Try this. You can hide the helper column.

[Glenn Kennedy]

I should have added... the first total is ALWAYS the first complete 5 minute period.

[zeegerman]

Hi Glenn,

Fantastic, I've just had a look and that's perfect. Thanks for all your help!

[zeegerman]

Hi Bas,

Thanks so much for taking a look at this. Your solution works really well, and I will also be able to use this.

All the best!

[zeegerman]

Hi,

Thanks for having a look at this, but the problem's been solved.

Warm regards!

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

[zeegerman]

Hi Glenn,

sure thing, will do. Have a great evening.