this is my problem:

i got a master worksheet and there are a lot of parameters in it. for
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 that
month(e.g september)

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

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

i hope that i made myself clear.

any help is greatly appreciated.


--
cjjoo
------------------------------------------------------------------------
cjjoo's Profile: http://www.excelforum.com/member.php...o&userid=26916
View this thread: http://www.excelforum.com/showthread...hreadid=470696

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


cjjoo wrote:
this is my problem:

i got a master worksheet and there are a lot of parameters in it. for
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 that
month(e.g september)

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

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

i hope that i made myself clear.

any help is greatly appreciated.

On Mon, 26 Sep 2005 11:26:59 +0100, Roger Govier
wrote:

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


cjjoo wrote:
this is my problem:

i got a master worksheet and there are a lot of parameters in it. for
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 that
month(e.g september)

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

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

i hope that i made myself clear.

any help is greatly appreciated.



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
__________________________