Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help - subtotal using vlookup depending on variable month | Excel Discussion (Misc queries) | |||
Supplier scorecard | Excel Discussion (Misc queries) | |||
Select Minimum value and return the name of the supplier | Excel Worksheet Functions | |||
subtotal nesting errors new this month | Excel Worksheet Functions | |||
Need cell formula to subtotal gross by month for a quarter | Excel Discussion (Misc queries) |