I'm not familiar with pivot tables, and in reading about ways to use SUMIF
with multiple criteria, I only found out about SUMPRODUCT. This is
essentially what I'm trying to do:

Columns A, B, and C make up a list of transactions, with the date in A, the
nature of the transaction in B, and the amount in C.
A B C
Date Transaction Amount
1/25/2007 Withdraw 100
1/30/2007 Deposit 100
etc.

I want, for example, the sum of all deposits between 2 dates. I could use

=DSUM(A1:C65000,"Amount",E1:G2)

Where E1:G2 is

E F G
Date Date Transaction
1/23/2007 <1/27/2007 ="=Deposit"

But in reality, I need, for each week, one of these for Withdraws, another
for Deposits, another for payments, three more for each of three categories
of charges, and probably some more, which would be a lot of work to set up,
and would need done every time you want to update the sheet for the next
duration of time.

I could also use

=SUMPRODUCT((A2:A11I1)*(A2:A11<J1)*(B2:B11="Depos it")*(C2:C11))

Where I1 equals some date and J1 equals some date, but this slows things
down when calculating cells.

Will pivot tables work? If so, I'll definately look into it. Thanks for
all the help, everyone!



"jasonc" wrote:

A few comments:
It would be helpful to know how many criteria fields you have. A better
choice may be =sumif or a pivot table. Sumproduct is not really a query tool
-- you can use it that way as an array function, but that's not really what
it's for. Also, you could use =sum as an array as well, but that get's tricky
too. The DSUM implementation in excel was poorly thought thru way back in
the early 90s, and so you're right that you wind up with a bunch of
repetitive criteria all over the place.
Jason



"Adam Graham" wrote:

Is to post your problem on here and continue searching for the answer on your
own. Once you post your question and help is on its way, you will inevitably
figure it out on your own within a matter of minutes.

I can just turn off automatic cell calculation under 'options' and hit F9 to
calculate my totals, which is what I'll probably do.

Nonetheless, a more efficient formula would improve my worksheet and
suggestions are still very much appreciated.


"Adam Graham" wrote:

I was using DSUM to sum financial transactions based on a variety of
criteria, many of which involved dates. The problem for this is that I would
have to make a variety of tables of DSUM criteria (I apologize if I'm not
using the right terminology here) for each week, so to set the worksheet up
for an entire year would be tedious and time consuming, not to mention have a
plethera of potential for careless errors.

And so, I created a new worksheet to do the same thing using SUMPRODUCT, and
while this worksheet has the advantage of being updatable for a new week
simply by dragging an existing cell into the one below it, after entering a
new transaction, the cursor freezes for several seconds and at the bottom of
the screen it says 'calculating cells xx%' which takes time to raise to 100%.


So, in short, I have two solutions to the problem: Using DSUM makes the
worksheet hard to update, and using SUMPRODUCT makes the worksheet hard to
use. Is it simply a matter of changing a feature to allow new input while
cells are being calculated? Why is SUMPRODUCT so inefficient? Is there
another, equally mobile solution that is more efficient?

Any suggestions/similar experiences would be appreciated. Thank You.