Oil Change Service

[Papi]

I need to look at a running list and be able to find the last date and mileage when we serviced our vehicles compared to the last time the service was done (plus some others but will maybe be able to figure them out from these examples.

Once the last service is listed it should allow C2 to get the next expected service by the fixed mileage of i.e. 5000. I just entered the dates in C3 and C4 but need them to get the highest service date pertaining to Service in column B and return the mileage to match in D3. Also, to do the same with the previous servicing in C4 and place the mileage in D4.

Book2
	A	B	C	D
1		Description	Detail
2		AnticiateNextService	72,590
3		LastService	6-Aug	67,590
4		PreviousService	22-May	62,567
5		Servicevariance		5,023
6
7	Date	Category	Mileage
8	6-Aug	Service	67,590
9	5-Jul	Repair	62,500
10	4-Jun	Install	58,900
11	22-May	Service	62,567
12	17-Feb	Repair	60,000
Sheet1

 

[Dave Patton]

Try a Pivot Table or

C3 {=MAX(IF(B8:B12="Service",A8:A12))}
C4 {=MAX(IF(B8:B12="Service",IF(A8:A12 < C3 ,A8:A12)))}

D3 =SUMPRODUCT(--($A$8:$A$12=C3),--($B$8:$B$12="Service"),($C$8:$C$12))

N.B. The formulas in C3 and C4 are array formulas; enter them with Control + Shift + Enter not just Enter.

 

[Papi]

The formulas are great. Thanks Dave. I appreciate your help.

 

[Dave Patton]

Thanks for the feedback. Dave