Subtotal by Month and supplier
 

I need to do a subtotal on below to give me the total amount by month per
supplier.
e.g supplier ste01 for Jan09 will show 1604, and for feb09 it will show 137
and then onto the next supplier zed01 broke down by month again.

Supplier Date Amount
ste01 01/01/2009 1
ste01 06/01/2009 45
ste01 10/01/2009 1558
ste01 01/02/2009 15
ste01 17/02/2009 15
ste01 18/02/2009 48
ste01 19/02/2009 57
ste01 20/02/2009 2
zed01 01/01/2009 37
zed01 02/01/2009 41
zed01 06/01/2009 42
zed01 25/02/2009 4
zed01 26/02/2009 24
zed01 01/03/2009 2
zed01 02/03/2009 7
zed01 03/03/2009 75
zed01 04/03/2009 58

Subtotal by Month and supplier
 

Try,

=SUMPRODUCT((A1:A17="ste01")*(MONTH(B1:B17)=1)*(C1 :C17))

In practice I'd use cell references to hold ste01 and the nmonth being
searched for

=SUMPRODUCT((A1:A17=D1)*(MONTH(B1:B17)=D2)*(C1:C17 ))

Mike

"d7" wrote:

I need to do a subtotal on below to give me the total amount by month per
supplier.
e.g supplier ste01 for Jan09 will show 1604, and for feb09 it will show 137
and then onto the next supplier zed01 broke down by month again.

Supplier Date Amount
ste01 01/01/2009 1
ste01 06/01/2009 45
ste01 10/01/2009 1558
ste01 01/02/2009 15
ste01 17/02/2009 15
ste01 18/02/2009 48
ste01 19/02/2009 57
ste01 20/02/2009 2
zed01 01/01/2009 37
zed01 02/01/2009 41
zed01 06/01/2009 42
zed01 25/02/2009 4
zed01 26/02/2009 24
zed01 01/03/2009 2
zed01 02/03/2009 7
zed01 03/03/2009 75
zed01 04/03/2009 58

Subtotal by Month and supplier
 

d7;235520 Wrote:
I need to do a subtotal on below to give me the total amount by month
per
supplier.
e.g supplier ste01 for Jan09 will show 1604, and for feb09 it will show
137
and then onto the next supplier zed01 broke down by month again.

Supplier Date Amount
ste01 01/01/2009 1
ste01 06/01/2009 45
ste01 10/01/2009 1558
ste01 01/02/2009 15
ste01 17/02/2009 15
ste01 18/02/2009 48
ste01 19/02/2009 57
ste01 20/02/2009 2
zed01 01/01/2009 37
zed01 02/01/2009 41
zed01 06/01/2009 42
zed01 25/02/2009 4
zed01 26/02/2009 24
zed01 01/03/2009 2
zed01 02/03/2009 7
zed01 03/03/2009 75
zed01 04/03/2009 58

One way to solve this problem is using a Pivot Table and grouping by
month.
More info can be found 'here'
(http://www.contextures.com/xlPivot07.html)


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=65729