Sumif with a few criteria

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

Harald Staff

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
 
J

Jarod

I want it in a formula because I am going to be continuously updating the
spreadsheet. I want it to just automatically update.
 
H

Harald Staff

Of course.

Jarod said:
I want it in a formula because I am going to be continuously updating the
spreadsheet. I want it to just automatically update.
Click to expand...
 
R

Roger Govier

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
 
T

T. Valko

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)
 
Top