Automatic subtraction

Dear all,

I'm trying to prepare vehicle mileage report based on each vehicle number and date
is it possible to have in E a formula to do the subtraction of C based on same month in A ? same applies whenever vehicle number changes in B

thanks a lot for your usual help

Im a little confused by your example data, rows 1-2 make sense in that the difference between them is calculated, but then why are rows 4-6 the total? what makes that different to calculating the difference as per rows 1-2?

Sorry my mistake it should be C7-C5

Explain the numbers in column E.

Originally Posted by AliGW

Explain the numbers in column E.

number in E should be the difference between last and first value of C, based on if B has the same plate number and A has the same month
I'm trying to do subtraction of kms of each plate number for each month

I hope it is clear, and thank you all for your help

Originally Posted by josephteh

A long formula, there must be a better formula than this:

PHP Code: 

IF(AND(ROW(A2)=MAX((MONTH($A$2:$A$10)=MONTH(A2))*($B$2:$B$10=B2)*(ROW($A$2:$A$10))),AGGREGATE(14,6,$C$2:$C$10/((MONTH($A$2:$A$10)=MONTH(A2))*($B$2:$B$10=B2)),1)<>AGGREGATE(15,6,$C$2:$C$10/((MONTH($A$2:$A$10)=MONTH(A2))*($B$2:$B$10=B2)),1)),AGGREGATE(14,6,$C$2:$C$10/((MONTH($A$2:$A$10)=MONTH(A2))*($B$2:$B$10=B2)),1)-AGGREGATE(15,6,$C$2:$C$10/((MONTH($A$2:$A$10)=MONTH(A2))*($B$2:$B$10=B2)),1),IF(AGGREGATE(14,6,$C$2:$C$10/((MONTH($A$2:$A$10)=MONTH(A2))*($B$2:$B$10=B2)),1)=AGGREGATE(15,6,$C$2:$C$10/((MONTH($A$2:$A$10)=MONTH(A2))*($B$2:$B$10=B2)),1),AGGREGATE(14,6,$C$2:$C$10/((MONTH($A$2:$A$10)=MONTH(A2))*($B$2:$B$10=B2)),1),"")) 


Thanks for your effort and time however when i inserted real values, the formula worked well till E39 !
when i copied all values down in J,K,L,M,O and E i got #NUM!

how to fix it ? thanks

set g2=1

in G3

=IF(AND(MONTH(A3)=MONTH(A2),YEAR(A3)=YEAR(A2)),G2,G2+1)

copy down

in F3

Formula:

Please Login or Register  to view this content.