Sumif with a few criteria

  • Thread starter Thread starter Jarod
  • Start date Start date
J

Jarod

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.
 
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
 
I want it in a formula because I am going to be continuously updating the
spreadsheet. I want it to just automatically update.
 
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
 
Use cells to hold the criteria...

F2 = Jason
G2 = 1
H2 = Municipal

=SUMPRODUCT(--(A2:A7=F2),--(B2:B7=G2),--(C2:C7=H2),D2:D7)

If you're using Excel 2007:

=SUMIFS(D2:D7,A2:A7,F2,B2:B7,G2,C2:C7,H2)
 
Back
Top