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!