Subtotal by Month and supplier

D

d7

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
 
M

Mike H

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
 
P

Pecoflyer

d7;235520 said:
I need to do a subtotal on below to give me the total amount by mont
per
supplier.
e.g supplier ste01 for Jan09 will show 1604, and for feb09 it will sho
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 b
month.
More info can be found 'here
(http://www.contextures.com/xlPivot07.html

--
Pecoflye

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

*Adding your XL version* to your post helps finding solution faste
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top