Return a value for a range of dates

G

Guest

I'm new to the forum and really hope you guys can help me out.

My spreadsheet has 9 columns. Col A has the date and time (eg 4/12/06 4:00
AM). The last column (J) is the difference between the consecutive cells with
the dates. For eg. Cell A2 is 4/12/06 4:00 AM ; A3 is 4/12/06 7:45 AM.
J3 calculates the difference as 3.75 hrs (A3-A2). This continues for 90
rows down.

I would like a formula or method where I could select a date range or a
particular period (start and end OR min and max) within the 90 rows and sum
the respective values for the same period in the last column.
 
B

Biff

Hi!

Use 2 cells to hold the criteria:

A1 = start
B1 = end

The criteria can be either just a date or both a date and time or a
combination of of date date/time.

=SUMPRODUCT(--(A2:A91>=A1),--(A2:A91<=B1),J2:J91)

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Difference between two date/time values 5
Lookups 1
match formula needed 2
help with datedif function please 4
separating date and time 5
How can I add the values based the color of font and the date 1
Comparison of columns using Conditional Formatting 3
Weighted Average Calc for duplicated values only 0

Top