DSUM or SUMPRODUCT wont work.

[Nimit Mehta]

Yesterday i posted a querry about DSUM and someone
suggested me to use SUMPRODUCT instead of DSUM. Well i
cant use SUMPRODUCT because what i want to do is this.

I have 800 workbooks of different customers all in one
folder.
All contain this.

Date Desposit Advance Minutes
16-mar-04 400.00 blah blah
18-mar-04 260.00 blah blah
18-mar-04 600.00 blah blah

Aim is to find the total desposit on say 18-mar-04. This
one sheet itself has 2 entries dated 18-march and can have
more, so these two must be sumed up + formula must look
for "18-mar" and sum up the despoit in all 800 sheets!
DSUM works fine, but it would work only if i keep all 800
workbooks open (which i cant ). DSUM only works with
active workbook. I cant link DSUM with workbook thats not
active or open, if i link it wont calculate. SUMIF works,
SUM works, DMAX works when linked even if workbook is not
active. I dont know if i can use SUMPRODUCT here, ummmm
any suggestions?

 

[Peo Sjoblom]

Please stay in the same thread! Yes you can use sumproduct since you say
that
sumif would work. If you checked the link I gave you could see that I
conditionally summed
values on a closed workbook. The questions is how slow it will be depending
on if the workbooks
are large many formulas etc..
For another way see

http://www.bygsoftware.com/examples/sqlrequest.html


example of sumproduct doing the same thing as sumif

=SUMIF(A2:A100,">=2",B2:B100)

=SUMPRODUCT(--(A2:A100>=2),B2:B100)