Sum values between a date range in excel 2

[Deano1976]

Microsoft Excel 2000.

I have three columns as below:

Date Time Litres

I want to be able to sum the litres between a specified date and time range. (This range will be determined by the contents of four more cells containing Start Date, Start Time, End Date and End Time - these cells can have the values changed by the user in order to re-calculate the litres).

How can this be done - am I missing something really simple?

 

[xlbo]

Use SUMIF and logic

If you want to know the values between 2 things, simply sum up the total that is less than the end date and subtract from it the total that is less than the start date

=SUMIF(DateRange,<=EndDate+EndTime,LitresRange) - SUMIF(DateRange,<=StartDate+StartTime,LitresRange)

 

[Deano1976]

I can't get the <= to work. Excel keeps putting this part of the equation into "".

 

[xlbo]

what have you tried? We can't help you unless you show us what you have tried and what doesn't work.
the example in the help file should show you how it should be done:

=SUMIF(A2:A5,">160000",B2:B5)

My formula wasn't an answer per se - it is just an example using named ranges to make it easier to understand

 

[Deano1976]

OK I have tried the following:

Cell C6 contains START DATE
Cell D6 contains START TIME
Cell C7 contains END DATE
Cell D7 contains END TIME

These cells can have the data changed at any time depending on what range of dates/time I want to sum.

Cell E6 contains =C6+D6
Cell E7 contains =C7+D7

My range of cells for the moment is C9 to C12 for the Date and D9 and D12 for the time. I have also added these together in cells E9 to E12 (C9+D9 etc).

I am therefore using a formula to sum between a range of dates/times as follows:

=SUMIF(E9:E12,<=E7,M9:M12)-SUMIF(E9:E12,<=E6,M9:M12)

This however is not working as excel keeps making <=E7 as "<=E7" and <=E6 as "<=E6".

 

[KenWright]

=SUMIF(E9:E12,"<="&E7,M9:M12)-SUMIF(E9:E12,"<="&E6,M9:M12)

Haven't checked your formula for logic, but in terms of syntax give thsi a go.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Deano1976]

Ken,

That's sorted it. Much appreciated.