Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
Hi everyone,
I've had several problems with excel the last year but after many hours of thinking I solved them (with some ideas from you guys, so thank you). This time I'm stuck. Can't think nothing that will give me the answer. So, suppose that I have an excel sheet were I store dates (m/d/yyyy) in A1:An, product codes in B1:Bn, customer codes in C1:Cn, Quantities in D1:Dn and Prices in E1:En. In a separate sheet of the same book, I want to find the sales for each month of each year (1/2006, 2/2006....etc). I know I have to use sumproduct, but I cannot find a way to expand the correct dates and then use them. Example: This is the sheet were I store all my information: 1/2/06 57-60-275 Cu-90.927 5 16.33 1/7/06 112-20-940 Cu-60.634 6 9.72 1/13/06 64-70-968 Cu-60.443 10 12.08 1/13/06 57-60-749 Cu-90.003 9 9 1/13/06 108-70-682 Cu-90.991 4 12.55 1/24/06 8-90-952 Cu-90.076 7 7.98 1/27/06 29-80-036 Cu-60.182 6 5.49 1/28/06 64-20-504 Cu-30.757 8 12.12 2/3/06 29-60-460 Cu-30.848 10 12.09 2/5/06 18-20-601 Cu-90.022 5 12.41 2/8/06 39-80-625 Cu-30.911 6 5.76 2/11/06 128-20-209 Cu-30.061 8 5.8 2/13/06 108-30-188 Cu-30.380 4 16.21 2/17/06 57-10-329 Cu-60.593 4 15.73 2/17/06 43-30-574 Cu-60.335 3 14.96 and here what I want to do: Month Total Sales 01-2006 02-2006 03-2006 I really don't have ANY idea. I think I have to use vlookup in array type, but still... If a solve this, then the rest of my work is easier. Can anyone help? Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
Ste wrote:
Hi everyone, I've had several problems with excel the last year but after many hours of thinking I solved them (with some ideas from you guys, so thank you). This time I'm stuck. Can't think nothing that will give me the answer. So, suppose that I have an excel sheet were I store dates (m/d/yyyy) in A1:An, product codes in B1:Bn, customer codes in C1:Cn, Quantities in D1:Dn and Prices in E1:En. In a separate sheet of the same book, I want to find the sales for each month of each year (1/2006, 2/2006....etc). I know I have to use sumproduct, but I cannot find a way to expand the correct dates and then use them. Example: This is the sheet were I store all my information: 1/2/06 57-60-275 Cu-90.927 5 16.33 You have a few options. I'm guessing total sale on each row is Qty*Price, in which case you will need to add this calculated field to your source data (call it Ext Price) if you want to use the Pivot Table solution. Pivot Table: You will need to add column headers first. Create a PT on the data, put Sale Date in the Row Area and Ext Price in the data area. Use the PT grouping function to group Sale Date by Month. Note: A calculated field in the PT itself will not work here--you must add the calculation to your data. SUMPRODUCT: =SUMPRODUCT(--($A28=DATE(YEAR($A$2:$A$18),MONTH($A$2:$A$18),1)), ($E$2:$E$18*$D$2:$D$18)) Where $A28 is the month to check (1/1/2006, 2/1/2006, etc.) Extend $A$18, etc., to fit your range (put your data in a List first to have this range update automagically). Array SUM: =SUM(IF($A28=DATE(YEAR($A$2:$A$18),MONTH($A$2:$A$1 8),1),$E$2:$E$18*$D$2:$D$18,0)) Enter as an array formula (Ctrl+Shift+Enter) and adjust ranges as above. Hope this helps! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotals
HI,
You can do it this way add one col next to date col (as month) then put formula =month(a13) and then do the subtotaling. Change at each month. This will give you the desire resutl eg given below. Date Months Prod Code Cust Code QTY Price 1/2/2006 1 57-60-275 Cu-90.927 5 16.33 1/7/2006 1 112-20-940 Cu-60.634 6 9.72 1/13/2006 1 64-70-968 Cu-60.443 10 12.08 1/13/2006 1 57-60-749 Cu-90.003 9 9 1/13/2006 1 108-70-682 Cu-90.991 4 12.55 1/24/2006 1 8-90-952 Cu-90.076 7 7.98 1/27/2006 1 29-80-036 Cu-60.182 6 5.49 1/28/2006 1 64-20-504 Cu-30.757 8 12.12 1 Total 85.27 2/3/2006 2 29-60-460 Cu-30.848 10 12.09 2/5/2006 2 18-20-601 Cu-90.022 5 12.41 2/8/2006 2 39-80-625 Cu-30.911 6 5.76 2/11/2006 2 128-20-209 Cu-30.061 8 5.8 2/13/2006 2 108-30-188 Cu-30.380 4 16.21 2/17/2006 2 57-10-329 Cu-60.593 4 15.73 2/17/2006 2 43-30-574 Cu-60.335 3 14.96 2 Total 82.96 Grand Total 168.23 -- _______________________ Click "Yes" button if it helps ________ Thanks Suleman Peerzade "Ste" wrote: Hi everyone, I've had several problems with excel the last year but after many hours of thinking I solved them (with some ideas from you guys, so thank you). This time I'm stuck. Can't think nothing that will give me the answer. So, suppose that I have an excel sheet were I store dates (m/d/yyyy) in A1:An, product codes in B1:Bn, customer codes in C1:Cn, Quantities in D1:Dn and Prices in E1:En. In a separate sheet of the same book, I want to find the sales for each month of each year (1/2006, 2/2006....etc). I know I have to use sumproduct, but I cannot find a way to expand the correct dates and then use them. Example: This is the sheet were I store all my information: 1/2/06 57-60-275 Cu-90.927 5 16.33 1/7/06 112-20-940 Cu-60.634 6 9.72 1/13/06 64-70-968 Cu-60.443 10 12.08 1/13/06 57-60-749 Cu-90.003 9 9 1/13/06 108-70-682 Cu-90.991 4 12.55 1/24/06 8-90-952 Cu-90.076 7 7.98 1/27/06 29-80-036 Cu-60.182 6 5.49 1/28/06 64-20-504 Cu-30.757 8 12.12 2/3/06 29-60-460 Cu-30.848 10 12.09 2/5/06 18-20-601 Cu-90.022 5 12.41 2/8/06 39-80-625 Cu-30.911 6 5.76 2/11/06 128-20-209 Cu-30.061 8 5.8 2/13/06 108-30-188 Cu-30.380 4 16.21 2/17/06 57-10-329 Cu-60.593 4 15.73 2/17/06 43-30-574 Cu-60.335 3 14.96 and here what I want to do: Month Total Sales 01-2006 02-2006 03-2006 I really don't have ANY idea. I think I have to use vlookup in array type, but still... If a solve this, then the rest of my work is easier. Can anyone help? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals problem: Excel 2003 (not, AFAIK, the nested subtotals bug) | Excel Discussion (Misc queries) | |||
Original subtotals should not be within nested subtotals in excel | Excel Worksheet Functions | |||
Subtotals: Nested subtotals below higher subtotal | Excel Discussion (Misc queries) | |||
How do I copy an outline w/ subtotals & paste just the subtotals | Excel Discussion (Misc queries) | |||
Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) |