SUM data in column based on Date/Time range

[t.jones]

Hi all

Tried some searches to solve this but couldn't find exactly how to do this.

I have a long list of dates and times in column A, beginning 01/01/2013 00:00 and proceeding in 5 minute steps. In column B I have measured rainfall amounts for each 5 minute step.

What I want to do is calculate the total amount of rain in intervals of 1,2,3....30 days prior to a specific date and time. So let's say that time is 30/01/2013 12:00, I therefore want to work out the total rain from 29/01/2013 12:05 to 30/01/2013 12:00 and then from 28/01/2013 12:05 to 30/01/2013 12:00, etc.

Can anyone advise on this please?

Thank you,
Tim.

[Tony Valko]

Try this...

Use cells to hold the date/time criteria:

D2 = lower date/time boundary
E2 = upper date/time boundary

Then, for the total rainfall within that time period:

=SUMPRODUCT(--(A2:A1000>=D2),--(A2:A1000<=E2),B2:B1000)

Adjust the ranges to suit.

[t.jones]

That's perfect, thanks so much!

[Tony Valko]

You're welcome. Thanks for the feedback!