How to make a sum equation under multi sorting

**macjunior** · 03-22-2010, 12:44 AM

Year Client Fruit Type Categories Sales Amount
2009 Market 1 Apple Fresh Alan $5.00
2009 Market 1 Orange Frozen Susan $3.30
2008 Market 2 Orange Fresh Alan $5.00
2007 Market 3 Apple Fresh Sue $7.00
2010 Market 2 Pineapple Frozen Tom $8.80
2009 Market 1 Strawberry Fresh John $4.50
2008 Market 1 Strawberry Frozen John $4.50
2010 Market 1 Strawberry Fresh John $4.50

I have an excel worksheet as above (or please see the attachment), I was wondering if it is possible to sum the amount without using VB under the folllowing condition :

1) Year (2009) + Fruit Type( Orange) + Sales(John) ,
2) Year (2009) + Fruit Type( Orange) + Categories (Fresh) + Sales(John) ,

Many thanks in advance
John

**darkyam** · 03-22-2010, 01:39 AM

In the future, it's best to post a workbook rather than a screen shot.

That being said, you can use Sumproduct.
=Sumproduct((A2:A9=2009)*(C2:C9="Orange")*(E2:E9="John"))

**macjunior** · 03-22-2010, 02:51 AM

Originally Posted by darkyam

In the future, it's best to post a workbook rather than a screen shot.

That being said, you can use Sumproduct.
=Sumproduct((A2:A9=2009)*(C2:C9="Orange")*(E2:E9="John"))

Thanks darkyam for the reply.

However, I think I didn't make myself clear. Actually, I would like to use formula to get the total amount (sum of column F) where Column A is set to "2009" + Column C is set to "Orange" + Column E is set to "Susan".

Thanks again.
John

**darkyam** · 03-22-2010, 08:10 AM

=Sumproduct((A2:A9=2009)*(C2:C9="Orange")*(E2:E9="John")*(F2:F9)

**macjunior** · 03-22-2010, 09:43 PM

Originally Posted by darkyam

=Sumproduct((A2:A9=2009)*(C2:C9="Orange")*(E2:E9="John")*(F2:F9)

Thanks again darkyam, I really appreciate it.

How to make a sum equation under multi sorting

LinkBack

Thread Tools

Rate This Thread

Display

How to make a sum equation under multi sorting

Re: How to make a sum equation under multi sorting

Re: How to make a sum equation under multi sorting

Re: How to make a sum equation under multi sorting

Re: How to make a sum equation under multi sorting

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions