Subtotals

  • Thread starter Thread starter Ste
  • Start date Start date
S

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 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.
 
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
 
Back
Top