y i cannot solve it ?

  • Thread starter Thread starter cjjoo
  • Start date Start date
C

cjjoo

this is my problem:

i got a master worksheet and there are a lot of parameters in it. fo
instance:

i got the

column 1: date
column 2: vehicle num
column 3: km travelled

in the next worksheet,

i want the sum of the distance travelled for each vehicle for tha
month(e.g september)

the master worksheet is the data base where the end -user will key i
the datas daily. but i need to sum the km travelled for each individua
vehicle num. How do i do that ? Is there a need to give a unqiue ID fo
each vehicle and its corresponding date?

i have tired to use sumproduct() but it seems that it cant sum th
total for that month only.

i hope that i made myself clear.

any help is greatly appreciated
 
Hi

Sumproduct would work
=SUMPRODUCT(--MONTH($A$2:$A$100)=9),--($B$2:$B$1000="your vehicle
no."),$C$2:$C$1000)

However, your data would be better summarised with a Pivot Tables, where you
could view totals for all vehicles for all months very easily.
Take a look at
http://peltiertech.com/Excel/Pivots/pivotstart.htm
for help in getting started with PT's


Regards

Roger Govier
 
Hi

Sumproduct would work
=SUMPRODUCT(--MONTH($A$2:$A$100)=9),--($B$2:$B$1000="your vehicle
no."),$C$2:$C$1000)

However, your data would be better summarised with a Pivot Tables, where you
could view totals for all vehicles for all months very easily.
Take a look at
http://peltiertech.com/Excel/Pivots/pivotstart.htm
for help in getting started with PT's


Regards

Roger Govier
Click to expand...

Assuming you want a general solution, one solution is as follows.

On your second sheet put the start and end dates of the period in
question in say A1 and B1

Then assuming you have a list of vehicle numbers starting in A4 on the
second sheet, and your master data is in A2:D10 on Sheet1, enter the
following in B4 on the second sheet and copy down.

=SUMPRODUCT((Sheet1!$B$2:$B$10=Sheet2!A4)*(Sheet1!A$2:$A$10>=Sheet2!A$1)*(Sheet1!$A$2:$A$10<=Sheet2!B$1)*(Sheet1!$C$2:$C$10))

(this may have wrapped round)

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
 
Back
Top