Close...try the below (again Array entered)
=MAX(IF((A$1:A$100<=F1)*(B$1:B$100=F2),C$1:C$100))

Good day Robbie...

If this post helps click Yes
---------------
Jacob Skaria


"Robbie" wrote:

Good Day Jacob

To workout the min and max in the same date range in order for me to work
out the km's done, would you agree with :

MAX(IF((A$1:A$100)<=F$1)*(B$1:B$100=F2),C$1:C$100) )

and this one in H2 to get the minimum:

=MIN(IF((A$1:A$100=F$1)*(B$1:B$100=F2),C$1:C$100, 10E10))

Thanking you for your time




"Jacob Skaria" wrote:

Try with your query dates in E1 and F1
E1 StartDate
F1 End Date
Change the reference to the truck number or reference to a acell

(all in one line)
=SUMPRODUCT(--(B2:B100="TruckReg"),--(A2:A100=E1),--(A2:A100<=F1),D2:D100)

(Adjust the range as required)

If this post helps click Yes
---------------
Jacob Skaria


"Robbie" wrote:

Good Day

I am currently using the below formula for min and max value by month(). I
would like to go a step further now and recover the sum value between
2 date values. ie. diesel used between January to April per vehicle

Appreciate the help

"Robbie" wrote:

Hi Pete

Thank you for your help, the adding F1 Month will certainly help.

Regards

"Pete_UK" wrote:

Use F1, say, to enter the month you are interested in (as a number 1
to 12), and list your registration numbers in column F starting with
F2. Then in G2 you can put this array* formula to get the maximum:

=MAX(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100))

and this one in H2 to get the minimum:

=MIN(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100,10E10))

I have assumed that you have up to 100 rows of data - adjust if you
have more.

*Array formulae need to be committed using the key combination of CTRL-
SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - you should not type these yourself.
If you amend the formula you must use CSE again.

However, you can use the normal copy/paste operation to copy G2:H2
down to cover the number of vehicles you have.

Change the number in F1 to choose a different month.

Hope this helps.

Pete

On May 5, 11:57 am, Robbie wrote:
I am trying to get a min and a max value in column C for the various
Registrations in column B in a spreadsheet - Excel 2003. ie trying to get the
first and last kilometer reading of the month per vehicle. Please can you help

A-DATE B-TRUCK REG C-KILOMETERS D-DIESEL FILLED
1 1/5/08 JWY467GP 10000 1000

2 5/5/08 PTS030GP 5000 500
3 6/5/08 MVT537GP 2000 250
4 7/5/08 JWY467GP 12000 1000
5 10/5/08 PTS030GP 6000 500
6 12/05/08 MVT537GP 2500 250