Advanced Criterea in Formulas
 

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?

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.

Advanced Criterea in Formulas
 

Assuming that they are true dates in the results table

=SUMPRODUCT(--(YEAR(Sheet1!$A$2:$A$20)=YEAR(B$1)),--(MONTH(Sheet1!$A$2:$A$20)=MONTH(B$1)),--(Sheet1!$B$2:$B$20=$A2),Sheet1!$C$2:$C$20)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"mpbhammer" wrote in message
...
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?