Subtotals

[Ste]

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 D1n 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.

 

[smartin]

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 D1n 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$18),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!

 

[Suleman Peerzade]

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