complicated sumproduct.

[Nimit Mehta]

Hello,
A folder contains about 1200 workbooks, named based on client's account
numbers who make international calls.

A workbook named 31648.xls looks like this.

Mark Twain
31648
DATE TIME COUNTRY RATE MINUTES AMOUNT

1-Jan-05 12:34 PM USA 1.95 6 11.70
1-Jan-05 11:20 PM USA 1.95 10 19.50
2-Jan-05 10:12 AM USA 1.95 4 7.80
3-Jan-05 11:03 AM USA 1.95 2 3.90
3-Jan-05 8:25 PM Canada 3.00 1 1.95
4-Jan-05 7:53 PM USA 1.95 10 19.50
4-Jan-05 10:04 PM UK 2.00 7 13.65
4-Jan-05 10:11 PM USA 1.95 13 25.35
5-Jan-05 10:17 PM USA 1.95 13 25.35
5-Jan-05 10:36 PM USA 1.95 23 44.85
6-Jan-05 7:58 AM USA 1.95 2 3.90
6-Jan-05 7:44 PM USA 1.95 6 11.70
6-Jan-05 8:50 PM USA 1.95 22 42.90
7-Jan-05 11:09 PM USA 1.95 7 13.65
9-Jan-05 10:51 PM AUS 3.00 3 5.85

I have made another workbook to get details of minutes consumed on a
perticular date from all 1200 workbooks using sumproduct funtion. This sheet
looks like this.


A B
F G
NAME MINUTES 9-Jan-05 1917.00
31648 3.00
31734 0.00
31777 0.00
31820 0.00
31863 0.00
31906 13.00
31949 0.00
31992 0.00
32078 0.00
32121 0.00
32207 0.00
32293 10.00
to 1200..

"Minutes" coloumn above contains this function for 31906.xls. (Account
number changes from cell to cell.)
=SUMPRODUCT(('F:\CONFERENCE\[31906.xls]Sheet1'!$A$5:$A$500=$F$1)*('F:\CONFERENCE\[31906.xls]Sheet1'!$E$5:$E$500))

G1 contains =SUM(B:B)

Right now, when i type a date in F1, sumproduct gets total minutes consumed
by all 1200 clients on that perticular date. What i want is a coloumn "DATE"
and coloumn next to it should display the minutes consumed on that perticular
date.

Like this :

I enter date in coloumn A and get minutes automatically in coloumn B.

Tuesday, May 24, 2005 1559
Wednesday, May 25, 2005 1721
Thursday, May 26, 2005 1789
Friday, May 27, 2005 1699
Saturday, May 28, 2005 2648
Sunday, May 29, 2005 2460
Monday, May 30, 2005 1940
Tuesday, May 31, 2005 1719
Wednesday, June 01, 2005 1793
Thursday, June 02, 2005 1396
Friday, June 03, 2005 1794
Saturday, June 04, 2005 1970
Sunday, June 05, 2005 2745
Monday, June 06, 2005 1207
Tuesday, June 07, 2005 1917

Right now i am manually inserting dates in coloumn B, after getting the
minutes for individual dates using the above sheet. This is a very slow
process, because once i enter a date in F1, sheet takes about 2 minutes to
calculate minutes on that perticular Date.

Suggestions?
Thanks.

 

[Duke Carey]

Well, you could use a Data Table (Data>Table...). Your Sum(B:B) formula
would be at the top of the table and the dates would go down the left column.
It'll take a long time to calculate, but should do the trick. I'd try it
with a samll sample of dates first - maybe 3 to 5 dates - and then expand it
to the full range of dates if it gives you what you want.

Personally, I'd see about the possibility of putting this into a database -
Access, MSDE, or SQL Server - and running queries or reports against the
database. That's one area where databases have an enormous advantage over
Excel.

Hello,
A folder contains about 1200 workbooks, named based on client's account
numbers who make international calls.

A workbook named 31648.xls looks like this.

Mark Twain
31648
DATE TIME COUNTRY RATE MINUTES AMOUNT

1-Jan-05 12:34 PM USA 1.95 6 11.70
1-Jan-05 11:20 PM USA 1.95 10 19.50
2-Jan-05 10:12 AM USA 1.95 4 7.80
3-Jan-05 11:03 AM USA 1.95 2 3.90
3-Jan-05 8:25 PM Canada 3.00 1 1.95
4-Jan-05 7:53 PM USA 1.95 10 19.50
4-Jan-05 10:04 PM UK 2.00 7 13.65
4-Jan-05 10:11 PM USA 1.95 13 25.35
5-Jan-05 10:17 PM USA 1.95 13 25.35
5-Jan-05 10:36 PM USA 1.95 23 44.85
6-Jan-05 7:58 AM USA 1.95 2 3.90
6-Jan-05 7:44 PM USA 1.95 6 11.70
6-Jan-05 8:50 PM USA 1.95 22 42.90
7-Jan-05 11:09 PM USA 1.95 7 13.65
9-Jan-05 10:51 PM AUS 3.00 3 5.85

I have made another workbook to get details of minutes consumed on a
perticular date from all 1200 workbooks using sumproduct funtion. This sheet
looks like this.


A B
F G
NAME MINUTES 9-Jan-05 1917.00
31648 3.00
31734 0.00
31777 0.00
31820 0.00
31863 0.00
31906 13.00
31949 0.00
31992 0.00
32078 0.00
32121 0.00
32207 0.00
32293 10.00
to 1200..

"Minutes" coloumn above contains this function for 31906.xls. (Account
number changes from cell to cell.)
=SUMPRODUCT(('F:\CONFERENCE\[31906.xls]Sheet1'!$A$5:$A$500=$F$1)*('F:\CONFERENCE\[31906.xls]Sheet1'!$E$5:$E$500))

G1 contains =SUM(B:B)

Right now, when i type a date in F1, sumproduct gets total minutes consumed
by all 1200 clients on that perticular date. What i want is a coloumn "DATE"
and coloumn next to it should display the minutes consumed on that perticular
date.

Like this :

I enter date in coloumn A and get minutes automatically in coloumn B.

Tuesday, May 24, 2005 1559
Wednesday, May 25, 2005 1721
Thursday, May 26, 2005 1789
Friday, May 27, 2005 1699
Saturday, May 28, 2005 2648
Sunday, May 29, 2005 2460
Monday, May 30, 2005 1940
Tuesday, May 31, 2005 1719
Wednesday, June 01, 2005 1793
Thursday, June 02, 2005 1396
Friday, June 03, 2005 1794
Saturday, June 04, 2005 1970
Sunday, June 05, 2005 2745
Monday, June 06, 2005 1207
Tuesday, June 07, 2005 1917

Right now i am manually inserting dates in coloumn B, after getting the
minutes for individual dates using the above sheet. This is a very slow
process, because once i enter a date in F1, sheet takes about 2 minutes to
calculate minutes on that perticular Date.

Suggestions?
Thanks.