y i cannot solve it ?

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.

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
<[email protected]> 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
__________________________