Advanced Criterea in Formulas
On May 7, 11:54*am, mpbhammer
wrote:
I'm trying to figure out the best way to use advanced criterea in formulas.. *
Here's my example:
Date * * * * * * *Account * * * * * * Amount
1/1/08 * * * * * account a * * * * * -$5
2/2/08 * * * * * account b * * * * * $10
3/3/08 * * * * * account a * * * * * *-$4
4/3/08 * * * * * account c * * * * * *$12
...and so on for all my financial transactions
my second worksheet is set up as follows
Account * * * * Jan 08 * * * * * * Feb 08 * * * * Mar 08 * * * Apr 08
account a * * *
account b * * *
account c
Now in each of the columns for the second worksheet I want to enter a
formula that will calculate the total of all transactions for the specific
account during the specific month. *I thought maybe the sumif function could
do this, but I can't figure out how to specify multiple criterea in that
function. *All I can figure is that I have to set up a seperate criterea
table for each cell to reference (which seems like a lot of work if I have 10
different accounts and I'm looking for monthly calculations over the past 5
years! * Isn't there a way to enter different multiple criterea for different
formulas without needing to create multiple criterea tables?
SUMPRODUCT could be used to do it, but why not simply use a pivot
table - you would need to have another column to convert the date into
the month, but other than that the pivot table would give you the
report automatically.
|