DSUM or SUMPRODUCT?

[Nimit Mehta]

A folder contains 1100 files of international calling
customers. All .xls files are named unique (client's name)
and have same fileds in all.

Coloumn A Coloumn B

DATE MINUTES

22-june-04 54
22-june-04 34
23-june-04 12



Another client.xls

Coloumn A Coloumn B

DATE MINUTES

16-june-04 54
22-june-04 72
23-june-04 12

One main reports.xls files is linked with all clients from
where i can just open the file and see current balance,
total deposits and stuff about all clients at one go,
without opening individual files.

I am looking for a way to check the daily consumed minutes.
I want to have a fuction where in if i enter 22-june-04 in
a cell, it should look for 22-june04 in all 1200 files and
add up minutes in same row. ( in above case should give
160 minutes, 2 calls from first client adding up to 88 and
1 call from another client.xls 72, total 160.)
Please Note: VLOOKUP wont work as it looks for first match
only, one client can have more than one calls made on the
same day itself.
DSUM works, but i have to keep all sheets open. Keeping
all 1200 sheets open would need 8 high speed processors
and GBs of RAM..
SUMPRODUCT might work.
Any suggestions?
Thanks a lot.

 

[Nimit Mehta]

-----Original Message-----
A folder contains 1100 files of international calling
customers. All .xls files are named unique (client's name)
and have same fileds in all.

Coloumn A Coloumn B

DATE MINUTES

22-june-04 54
22-june-04 34
23-june-04 12



Another client.xls

Coloumn A Coloumn B

DATE MINUTES

16-june-04 54
22-june-04 72
23-june-04 12

One main reports.xls files is linked with all clients from
where i can just open the file and see current balance,
total deposits and stuff about all clients at one go,
without opening individual files.

I am looking for a way to check the daily consumed minutes.
I want to have a fuction where in if i enter 22-june-04 in
a cell, it should look for 22-june04 in all 1200 files and
add up minutes in same row. ( in above case should give
160 minutes, 2 calls from first client adding up to 88 and
1 call from another client.xls 72, total 160.)
Please Note: VLOOKUP wont work as it looks for first match
only, one client can have more than one calls made on the
same day itself.
DSUM works, but i have to keep all sheets open. Keeping
all 1200 sheets open would need 8 high speed processors
and GBs of RAM..
SUMPRODUCT might work.
Any suggestions?
Thanks a lot.

.

 

[Ace Fekay [MVP]]

In
Curious to see the answer to this.

--
Regards,
Ace

Please direct all replies ONLY to the Microsoft public newsgroups
so all can benefit.

This posting is provided "AS-IS" with no warranties or guarantees
and confers no rights.

Ace Fekay, MCSE 2000, MCSE+I, MCSA, MCT, MVP
Microsoft Windows MVP - Active Directory

HAM AND EGGS: A day's work for a chicken;
A lifetime commitment for a pig.

 

[Nimit Mehta]

Been 5 days, havnt gotten any answers yet

 

[Ace Fekay [MVP]]

In

Been 5 days, havnt gotten any answers yet

Ok. Please post back if you hear anything.

Ace