Saturday, January 10, 2009   
  Search   
 
 
Register  Login  
Forums  
     Minimize  

Welcome to MeraWindows forums.

Thank you for being at the Microsoft Windows Community Site. You may have to register before posting in forums. It's absolutely free. After registering, you can get all the benefits available to our registered members, you can access our Downloads section, you can participate in contests, etc. You can post in forums in English as well as in Hindi, in fact we encourage you to use Hindi in your posts. If you have any problem with registration or login, please contact us.
     
  


 
  Microsoft Windows Forums  Microsoft Offic...  Microsoft Excel  Challenge I - Summing based on multiple conditions
Previous Previous
 
Next Next
New Post 9/22/2008 9:44 AM
User is offline Ashish Mathur
103 posts
www.ashishmathur.com
Senior Member




Challenge I - Summing based on multiple conditions 

Hi,

Say in column A, i have names, in column B i have revenue and in column C i have region.

Name Amount       Region
A 100   WE
B 101   RT
A 102   WE
F 103   IP
R 104   WE

 

The question now is "How can i sum up the amounts for A in region WE.  The result should be 100+102=202.

Let's see how you would solve it.

For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com
 
New Post 9/28/2008 11:51 AM
User is offline Hyder14
3 posts
Member


Re: Challenge I - Summing based on multiple conditions 

 

If the data is placed in Range (D4:E8) in a Worksheet, the following formula will give the desired result.

=SUMPRODUCT(((D48)="A")*((F4:F8)="  WE")*(E4:E8))

Regards,

 
 
New Post 9/28/2008 5:56 PM
User is offline Ashish Mathur
103 posts
www.ashishmathur.com
Senior Member




Re: Challenge I - Summing based on multiple conditions 

Hi,

Wow that was wonderful

For Microsoft Excel trainings in India, please visit http://ashishmathur.com/training.aspx

Regards,

Ashish Mathur - View Ashish Mathur's profile on LinkedIn
Microsoft Excel MVP
www.ashishmathur.com
 
New Post 9/28/2008 8:43 PM
User is offline Hyder14
3 posts
Member


Re: Challenge I - Summing based on multiple conditions 

Thanks, it was just a matter of having had the opportunity of using the highly useful function "Sumproduct", which comes in handy in a variety of conditions.

Regards,

 
 
New Post 9/29/2008 1:46 PM
User is offline bosco yip
1 posts
Member


Re: Challenge I - Summing based on multiple conditions 
Modified By bosco yip on 9/29/2008 1:50:23 PM)
If the data is located at A1:C6 with the heading at A1:C1, 
 
then, my formulas are :
 
1]   {=SUM((A2:A6="A")*(C2:C6="WE")*B2:B6)}
 
2]   {=SUM(((A2:A6="A")+(C2:C6="WE")=2)*B2:B6)}
 
3]   {=SUM((A2:A6&"@"&C2:C6="A"&"@"&"WE")*B2:B6)}
 
4]   {=SUM(IF(A2:A6="A",IF(C2:C6="WE",B2:B6)))}
 
5]   =SUMPRODUCT(--(A2:A6="A"),--(C2:C6="WE"),B2:B6)
 
6]   =SUMPRODUCT((((A2:A6="A")+(C2:C6="WE"))=2)*B2:B6)
 
7]   =SUMPRODUCT(--(A2:A6&"@"&C2:C6="A"&"@"&"WE"),B2:B6)
 
8]   {=SUMPRODUCT(IF(A2:A6="A",IF(C2:C6="WE",B2:B6)))}
 
9]   {=MMULT(TRANSPOSE((A2:A6="A")*(C2:C6="WE")),B2:B6)}
 
10]  {=MMULT(--TRANSPOSE(((A2:A6="A")+(C2:C6="WE"))=2),B2:B6)}
 
11]  {=MMULT(TRANSPOSE(--(A2:A6&"@"&C2:C6="A"&"@"&"WE")),B2:B6)}
 
12]  {=MMULT(TRANSPOSE(--IF(A2:A6="A",IF(C2:C6="WE",1))),B2:B6)}
 
Regards
Bosco
 
 Page 1 of 212Next 
Previous Previous
 
Next Next
  Microsoft Windows Forums  Microsoft Offic...  Microsoft Excel  Challenge I - Summing based on multiple conditions


   Get Your Own E-Mail Account @MeraWindows.com Minimize  
New Page 1 New Page 1
Show your cool quotient with @merawindows.com email account
     
  
 
 
Terms Of Use   Privacy Statement  
© 2008 MeraWindows.com