Advanced Criterea in Formulas

[mpbhammer]

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?

 

[aidan.heritage]

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 have10
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.

 

[Bob Phillips]

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)