A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sumif with a few criteria



 
 
Thread Tools Display Modes
  #1  
Old April 12th, 2010, 06:18 PM posted to microsoft.public.excel.newusers
Jarod
external usenet poster
 
Posts: 23
Default 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  
Old April 12th, 2010, 06:25 PM posted to microsoft.public.excel.newusers
Harald Staff
external usenet poster
 
Posts: 91
Default 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  
Old April 12th, 2010, 07:14 PM posted to microsoft.public.excel.newusers
Jarod
external usenet poster
 
Posts: 23
Default 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  
Old April 12th, 2010, 10:39 PM posted to microsoft.public.excel.newusers
Harald Staff[_2_]
external usenet poster
 
Posts: 198
Default 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  
Old April 12th, 2010, 10:48 PM posted to microsoft.public.excel.newusers
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default 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  
Old April 13th, 2010, 06:39 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.