# how to create formula for automatic subtraction

#### legolas

##### New Member
I'm trying to prepare vehicle mileage report based on vehicle number (column B) and date (A) , pls see attached
is it possible to have in E a formula to do the subtraction of last and first mileage in C based on same month in A ?

i just need to calculate how many kms a vehicle travelled each month

i hope it is clear enough and thanks for your help

#### Attachments

• 11.1 KB Views: 8

#### Peter Bartholomew

##### Well-Known Member
This is a 365 solution.
Code:
``````= LET(
startMonth, 1+EOMONTH(+date,-1),
distinct,   UNIQUE(HSTACK(startMonth,vehicle)),
start,      CHOOSECOLS(distinct, 1),
end,        EOMONTH(start, 0),
plate,      CHOOSECOLS(distinct, 2),
max, MAXIFS(mileage, vehicle, plate, date, "<="&end),
min, MINIFS(mileage, vehicle, plate, date, ">="&start),
HSTACK(TEXT(start,"mmm"), plate, max-min, max)
)``````

#### Attachments

• 13.5 KB Views: 5

#### legolas

##### New Member
when i insert the code alt+F11 i get lot of error
any help ? thanks

#### vletm

##### Excel Ninja
legolas
Could You use something like this?

#### Attachments

• 15 KB Views: 4

#### Peter Bartholomew

##### Well-Known Member
My code was a worksheet formula, not VBA. It is functionality that has been introduced to Excel over the past 4 years and is only available in Excel 365. HSTACK is only just coming out of beta-testing but the results could be returned column by column without it. I use defined names for all references, so 'date' and 'vehicle' are created using Name Manager. If the sheet I attached did not work, you most likely have a legacy version of Excel.