If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sumif with a few criteria
Example:
A B C D Person Period Sales Type Commision Mary 1 Industrial $500 Jason 1 Municipal $1000 Mary 2 Municipal $700 Jason 2 Industrial $1500 Mary 1 Municipal $750 Jason 1 Municipal $410 I would like to know, how much commision Jason had for his Municipal sales in period 1. |
#2
|
|||
|
|||
Sumif with a few criteria
Spend half an hour to google/learn Pivot Tables. Formula solutions may seem
familiar and safe and sufficient, but a Pivot is way faster and unbelievably powerful. Best wishes Harald "Jarod" skrev i melding news Example: A B C D Person Period Sales Type Commision Mary 1 Industrial $500 Jason 1 Municipal $1000 Mary 2 Municipal $700 Jason 2 Industrial $1500 Mary 1 Municipal $750 Jason 1 Municipal $410 I would like to know, how much commision Jason had for his Municipal sales in period 1. |
#3
|
|||
|
|||
Sumif with a few criteria
I want it in a formula because I am going to be continuously updating the
spreadsheet. I want it to just automatically update. "Harald Staff" wrote: Spend half an hour to google/learn Pivot Tables. Formula solutions may seem familiar and safe and sufficient, but a Pivot is way faster and unbelievably powerful. Best wishes Harald "Jarod" skrev i melding news Example: A B C D Person Period Sales Type Commision Mary 1 Industrial $500 Jason 1 Municipal $1000 Mary 2 Municipal $700 Jason 2 Industrial $1500 Mary 1 Municipal $750 Jason 1 Municipal $410 I would like to know, how much commision Jason had for his Municipal sales in period 1. . |
#4
|
|||
|
|||
Sumif with a few criteria
Of course.
"Jarod" wrote in message news I want it in a formula because I am going to be continuously updating the spreadsheet. I want it to just automatically update. "Harald Staff" wrote: Spend half an hour to google/learn Pivot Tables. Formula solutions may seem familiar and safe and sufficient, but a Pivot is way faster and unbelievably powerful. Best wishes Harald "Jarod" skrev i melding news Example: A B C D Person Period Sales Type Commision Mary 1 Industrial $500 Jason 1 Municipal $1000 Mary 2 Municipal $700 Jason 2 Industrial $1500 Mary 1 Municipal $750 Jason 1 Municipal $410 I would like to know, how much commision Jason had for his Municipal sales in period 1. . |
#5
|
|||
|
|||
Sumif with a few criteria
Hi Jarod
I would reinforce what Harald says. If you make your source data a List (XL2003) a Table(XL2007) or a Dynamic named Range (any XL version), then the new data will automatically be included. You can even make the refreshing of the Pivot Table automatic, whenever you activate it. For more help on Dynamic Ranges take a look at http://www.contextures.com/xlNames03.html -- Regards Roger Govier Jarod wrote: I want it in a formula because I am going to be continuously updating the spreadsheet. I want it to just automatically update. "Harald Staff" wrote: Spend half an hour to google/learn Pivot Tables. Formula solutions may seem familiar and safe and sufficient, but a Pivot is way faster and unbelievably powerful. Best wishes Harald "Jarod" skrev i melding news Example: A B C D Person Period Sales Type Commision Mary 1 Industrial $500 Jason 1 Municipal $1000 Mary 2 Municipal $700 Jason 2 Industrial $1500 Mary 1 Municipal $750 Jason 1 Municipal $410 I would like to know, how much commision Jason had for his Municipal sales in period 1. . |
#6
|
|||
|
|||
Sumif with a few criteria
Use cells to hold the criteria...
F2 = Jason G2 = 1 H2 = Municipal =SUMPRODUCT(--(A2:A7=F2),--(B2:B7=G2),--(C2:C7=H2),D27) If you're using Excel 2007: =SUMIFS(D27,A2:A7,F2,B2:B7,G2,C2:C7,H2) -- Biff Microsoft Excel MVP "Jarod" wrote in message news Example: A B C D Person Period Sales Type Commision Mary 1 Industrial $500 Jason 1 Municipal $1000 Mary 2 Municipal $700 Jason 2 Industrial $1500 Mary 1 Municipal $750 Jason 1 Municipal $410 I would like to know, how much commision Jason had for his Municipal sales in period 1. |
Thread Tools | |
Display Modes | |
|
|