SUMIF Problem

Sitios México · 04-27-2005, 10:09 PM

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 · 04-28-2005, 12:06 AM

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)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Sitios México" <[email protected]> wrote in message
news:[email protected]...
> 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...

Franz · 04-28-2005, 11:06 AM

"Ragdyer" <[email protected]>ha scritto nel messaggio
[email protected]

> 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

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

RagDyeR · 04-29-2005, 11:06 AM

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!
==============================================

"Franz" <[email protected]> wrote in message
news:[email protected]...
"Ragdyer" <[email protected]>ha scritto nel messaggio
[email protected]

> 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

----------------------------------------------------------------------------
------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------
------------

Franz · 05-03-2005, 07:06 AM

"RagDyeR" <[email protected]>ha scritto nel messaggio
[email protected]

> 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... :-)

--
Regards

Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

RagDyeR · 05-04-2005, 11:06 AM

Appreciate the feed-back.
--

Regards,

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

"Franz" <[email protected]> wrote in message
news:[email protected]...
"RagDyeR" <[email protected]>ha scritto nel messaggio
[email protected]

> 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... :-)

--
Regards

Franz

----------------------------------------------------------------------------
------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------
------------