Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assistance with advanced formulas | Excel Worksheet Functions | |||
how do i countif on multiple criterea? | Excel Worksheet Functions | |||
lookup table with specific criterea | Excel Worksheet Functions | |||
Advanced Excel formulas | Excel Discussion (Misc queries) | |||
help with "criterea" in the sumif function | Excel Worksheet Functions |