SUMIF Problem

[=?ISO-8859-1?Q?Sitios_M=E9xico?=]

Hello Readers, Helpers and all;

I've been having a headache trying to solve the Folowing:
I have a workbook containing several worksheets

NOTE: NON real data.

Sales Report Sheet Containig:
Date Model Price Comission
30. abr. 2005 7 $208,035.00 $3,120.525
18. abr. 2005 13 $353,026.00 $3,530.260
18. abr. 2005 13 $353,026.00 $3,530.260
15. abr. 2005 18 $535,751.00 $4,018.133
8. abr. 2005 12 $349,994.00 $3,499.940
3. abr. 2005 3 $83,073.00 $2,076.825
1. abr. 2005 5 $157,952.00 $2,369.280
29. mar. 2005 6 $200,878.00 $3,013.170
22. mar. 2005 17 $482,533.00 $4,825.330
16. mar. 2005 17 $482,533.00 $4,825.330
16. mar. 2005 4 $117,704.00 $2,354.080
5. mar. 2005 17 $482,533.00 $4,825.330


Based on the model The price is extracted from another worksheet named
Prices using a vlookup funcion

Model Price
1 $75,273.00
2 $81,300.00
3 $83,073.00
4 $117,704.00
5 $157,952.00
6 $200,878.00
7 $208,035.00
8 $220,653.00
9 $246,372.00
10 $263,614.00
11 $337,973.00
12 $349,994.00
13 $353,026.00
14 $367,234.00
15 $462,053.00
16 $470,022.00
17 $482,533.00
18 $535,751.00
19 $541,547.00
20 $559,197.00

And the comission is also extracted from another worksheet named
Comissions using also a vlookup funcion based on the price

Lower Limit Upper Limit Comission
$0.00 $70,000.00 3.00%
$70,001.00 $90,000.00 2.50%
$90,001.00 $120,000.00 2.00%
$120,001.00 $150,000.00 1.75%
$150,001.00 $250,000.00 1.50%
$250,001.00 $500,000.00 1.00%
$500,001.00 $1,000,000.00 0.75%
$2,000,000.00 $5,000,000.00 0.50%


What I would like to do is a summary of sales per month with a
conditional sum depending on the month of the sale..

Apr 05 $ 22,145.223
Mar 05 $ 19,843.240
and so on...

Is it possible to do that?

I've tried month(date) Year(date) and Sumif() but no luck...

I would appreciate the help..


Thanks
Oscar...

 

[Ragdyer]

Create a list, in say G1 to G12, containing real dates and format to show
the 12 months and the year.

With "real" dates in say A2:A30, and sales in C2:C30,
Try this in H1, and copy down to H12:

=SUMPRODUCT((TEXT($A$2:$A$30,"mm-yy")=TEXT(G1,"mm-yy"))*$C$2:$C$30)

 

[Franz]

Create a list, in say G1 to G12, containing real dates and format to
show the 12 months and the year.

With "real" dates in say A2:A30, and sales in C2:C30,
Try this in H1, and copy down to H12:

=SUMPRODUCT((TEXT($A$2:$A$30,"mm-yy")=TEXT(G1,"mm-yy"))*$C$2:$C$30)

Hi Ragdyer,
I tried your formula, but it doesn't worked for me, I think for two reasons:
1) you can't multiply "TRUE" and "FALSE" with numbers;
2) you have to enter the formula in hitting CRTL+SHIFT+ENTER.

Try this one:

=SUMPRODUCT(((IF(TEXT(Sheet1!A2:A4,"[$-410]mmmm;@")=Sheet2!A2,1,0))*(Sheet1!B2:B4)))

(Hit CRTL+SHIFT+ENTER instead ENTER)

On sheet1 range A2:A4 I have dates and range B2:B4 I have sales.
On sheet2 range A2:A13 I have months write down in text: January, February
and so on.

--
Hoping to be helpful...

Regards

Franz

 

[RagDyeR]

You're wrong on both counts.

Check out this link and maybe you'll understand:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Create a list, in say G1 to G12, containing real dates and format to
show the 12 months and the year.

With "real" dates in say A2:A30, and sales in C2:C30,
Try this in H1, and copy down to H12:

=SUMPRODUCT((TEXT($A$2:$A$30,"mm-yy")=TEXT(G1,"mm-yy"))*$C$2:$C$30)

Hi Ragdyer,
I tried your formula, but it doesn't worked for me, I think for two reasons:
1) you can't multiply "TRUE" and "FALSE" with numbers;
2) you have to enter the formula in hitting CRTL+SHIFT+ENTER.

Try this one:

=SUMPRODUCT(((IF(TEXT(Sheet1!A2:A4,"[$-410]mmmm;@")=Sheet2!A2,1,0))*(Sheet1!
B2:B4)))

(Hit CRTL+SHIFT+ENTER instead ENTER)

On sheet1 range A2:A4 I have dates and range B2:B4 I have sales.
On sheet2 range A2:A13 I have months write down in text: January, February
and so on.

--
Hoping to be helpful...

Regards

Franz

 

[Franz]

You're wrong on both counts.

Check out this link and maybe you'll understand:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Thanks for the link. Is very interesting. I discovered many things about
SUMPRODUCT, as the use of "--" (that I saw used on English speaking NG, but
not in the Italian one) that before I didn't understand the meaning of.

Moreover I have to ask you sorry, because I remade the test on your formula
and it works well. Maybe the first time I tried I made some mistakes...

 

[RagDyeR]

Appreciate the feed-back.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

You're wrong on both counts.

Check out this link and maybe you'll understand:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Thanks for the link. Is very interesting. I discovered many things about
SUMPRODUCT, as the use of "--" (that I saw used on English speaking NG, but
not in the Italian one) that before I didn't understand the meaning of.

Moreover I have to ask you sorry, because I remade the test on your formula
and it works well. Maybe the first time I tried I made some mistakes...