I am trying to calculate the commision on sales using the percentages in
A1:C1 for each rep using the following criteria:
For new sales for Products 1 or 2, Sum B10:B12 and G-I0:G12 then multiply by
C7
For Existing sales of Product 1 or 2, sum A18:A20 and G18:G20 and multiply
by A1
For increases for both I want to return the increased diference year over
year in Rows 18-20 and then multiply only the difference by B5

Thank you for any guidance you can provide. I have been at this for so long
that my head hurts for the effort.

To help clarify:
New sales for Product1 are in columns A, B C and Product2 in G, H I; Rows10-12
Existing sales are in Rows 18-20

Here is what the data looks like:
A B C D G
H I
1 3% 5% 7%

9 Rep 2005 2006 2007 2005 2006
2007
10 Joe $2,766.00 $2,884.00 $3,489.88 $600.00 $1,734.00 $533.65
11 Fred $1,857.00 $700.00 $0.0 $3,552.00 $2,886.00 $5,051.29
12 Sally $2,334.00 $3,894.00 $4,125.18 $11,065.99 $7,860.00
$6,254.12

18 Sally $2,766.00 $2,884.00 $3,489.88 $600.00 $1,734.00
$533.65
19 Fred $1,857.00 $700.00 $0.0 $3,552.00 $2,886.00 $5,051.29
20 Fred $2,334.00 $3,894.00 $4,125.18 $11,065.99 $7,860.00 $6,254.12

I forgot to mention that if the difference in existing sales results in a
negative number then it should be returned as a zero and not a negative
number that would reduce the other product calculation.

"ladara tech" wrote:

I am trying to calculate the commision on sales using the percentages in
A1:C1 for each rep using the following criteria:
For new sales for Products 1 or 2, Sum B10:B12 and G-I0:G12 then multiply by
C7
For Existing sales of Product 1 or 2, sum A18:A20 and G18:G20 and multiply
by A1
For increases for both I want to return the increased diference year over
year in Rows 18-20 and then multiply only the difference by B5

Thank you for any guidance you can provide. I have been at this for so long
that my head hurts for the effort.

To help clarify:
New sales for Product1 are in columns A, B C and Product2 in G, H I; Rows10-12
Existing sales are in Rows 18-20

Here is what the data looks like:
A B C D G
H I
1 3% 5% 7%

9 Rep 2005 2006 2007 2005 2006
2007
10 Joe $2,766.00 $2,884.00 $3,489.88 $600.00 $1,734.00 $533.65
11 Fred $1,857.00 $700.00 $0.0 $3,552.00 $2,886.00 $5,051.29
12 Sally $2,334.00 $3,894.00 $4,125.18 $11,065.99 $7,860.00
$6,254.12

18 Sally $2,766.00 $2,884.00 $3,489.88 $600.00 $1,734.00
$533.65
19 Fred $1,857.00 $700.00 $0.0 $3,552.00 $2,886.00 $5,051.29
20 Fred $2,334.00 $3,894.00 $4,125.18 $11,065.99 $7,860.00 $6,254.12