Hi Tom

Doesn't work for me for some reason. It is possible you can send me a the
workbook you used to get this to work.

My email

REMOVE 1971 for the correct email address

I am simply getting a 0 in my workbook

Regards

Jas

"Tom Ogilvy" wrote in message
...

=sumproduct((Data!$A$1:$A$500=A2)*(Data!$B$1:$B$50 0=B2)*)(Data!$B$1:$B$500<
=C2)*(Data!$D$1:$D$500))

where the sheet with the billing data is named Data.

Match the phone number, find the dates between the start and end dates for
that line - sum up the costs.

A2, B2, C2 refer to your lending data base.

Put this formula in G2, then drag fill down the column. Before copying,
Extend the 500 in each reference to include all the data in the DATA
sheet.

--
Regards,
Tom Ogilvy



"J P Singh" <noemail@asIhatespam wrote in message
...
Hi All

Can someone please help me put a solution in place for this problem.

We have two lists of data which we need to match.

We loan out phones to our business users who use them for fixed number
of
days and then return them to us. This data is stored in one worksheet as

Mobile Number Date From Date To User Prod Number
07855422282 12/03/2003 13/03/2003 Kiersty Smith 33089320
07855422282 23/04/2003 27/04/2003 Kiri Belcher 33097720
07855422282 19/06/2003 20/06/2003 Tim Day 10130610
07855412282 01/07/2003 05/07/2003 Jackie O'Shea 33453410
07855412282 07/08/2003 09/08/2003 Rhodi Evans 334616 ETW
07855412282 14/01/2003 16/01/2003 Andrew Orr 901101103


Once the users have used the phones we get a bill from our phone company
data in which looks like

Phone No Date Total Cost
07855422282 12/3/03 1.54
07855422282 13/3/03 1.20
07855422282 23/4/03 0.58
07855422282 24/4/03 1.45
07855422282 25/4/03 0.00
07855422282 26/4/03 0.96
07855422282 27/4/03 1.99
07855412282 07/8/03 1.54
07855412282 08/8/03 2.58
07855412282 09/8/03 0.58
07855412282 14/1/03 1.29
07855412282 15/1/03 2.66

What I need to do is to find out what was the total cost of calls for a
particular user for the time he has had the phone. I am sure this can be
done through VBA but not sure how to start.

Then results should look something like
Mobile Number Date From Date To User Total Cost
07855422282 12/03/2003 13/03/2003 Kiersty Smith £2.74
07855422282 23/04/2003 27/04/2003 Kiri Belcher
07855422282 19/06/2003 20/06/2003 Tim Day
07855412282 01/07/2003 05/07/2003 Jackie O'Shea
07855412282 07/08/2003 09/08/2003 Rhodi Evans
07855412282 14/01/2003 16/01/2003 Andrew Orr



Can you offer some help please.

Thanks

Workbook was emailed.

Regards,
Tom Ogilvy

"J P Singh" <noemail@asIhatespam wrote in message
...
Hi Tom

Doesn't work for me for some reason. It is possible you can send me a the
workbook you used to get this to work.

My email

REMOVE 1971 for the correct email address

I am simply getting a 0 in my workbook

Regards

Jas

"Tom Ogilvy" wrote in message
...


=sumproduct((Data!$A$1:$A$500=A2)*(Data!$B$1:$B$50 0=B2)*)(Data!$B$1:$B$500<
=C2)*(Data!$D$1:$D$500))

where the sheet with the billing data is named Data.

Match the phone number, find the dates between the start and end dates
for
that line - sum up the costs.

A2, B2, C2 refer to your lending data base.

Put this formula in G2, then drag fill down the column. Before copying,
Extend the 500 in each reference to include all the data in the DATA
sheet.

--
Regards,
Tom Ogilvy



"J P Singh" <noemail@asIhatespam wrote in message
...
Hi All

Can someone please help me put a solution in place for this problem.

We have two lists of data which we need to match.

We loan out phones to our business users who use them for fixed number
of
days and then return them to us. This data is stored in one worksheet
as

Mobile Number Date From Date To User Prod Number
07855422282 12/03/2003 13/03/2003 Kiersty Smith 33089320
07855422282 23/04/2003 27/04/2003 Kiri Belcher 33097720
07855422282 19/06/2003 20/06/2003 Tim Day 10130610
07855412282 01/07/2003 05/07/2003 Jackie O'Shea 33453410
07855412282 07/08/2003 09/08/2003 Rhodi Evans 334616 ETW
07855412282 14/01/2003 16/01/2003 Andrew Orr 901101103


Once the users have used the phones we get a bill from our phone
company
data in which looks like

Phone No Date Total Cost
07855422282 12/3/03 1.54
07855422282 13/3/03 1.20
07855422282 23/4/03 0.58
07855422282 24/4/03 1.45
07855422282 25/4/03 0.00
07855422282 26/4/03 0.96
07855422282 27/4/03 1.99
07855412282 07/8/03 1.54
07855412282 08/8/03 2.58
07855412282 09/8/03 0.58
07855412282 14/1/03 1.29
07855412282 15/1/03 2.66

What I need to do is to find out what was the total cost of calls for
a
particular user for the time he has had the phone. I am sure this can
be
done through VBA but not sure how to start.

Then results should look something like
Mobile Number Date From Date To User Total Cost
07855422282 12/03/2003 13/03/2003 Kiersty Smith £2.74
07855422282 23/04/2003 27/04/2003 Kiri Belcher
07855422282 19/06/2003 20/06/2003 Tim Day
07855412282 01/07/2003 05/07/2003 Jackie O'Shea
07855412282 07/08/2003 09/08/2003 Rhodi Evans
07855412282 14/01/2003 16/01/2003 Andrew Orr



Can you offer some help please.

Thanks