Sum Criterias

andy_2610

Board Regular
Hi All,

I have set boundaries for a commission based sheet..

Each pay periods is every 2 weeks so we will start at 1/1/2015-1/14/2015 to make it simple. Now here is the part I am stuck at..
If the sum of Column B is greater than 1000 between the pay period, I want to sum anything greater than 1000 at 80% so column C. I've tried using a helper column, but I keep getting stuck. In another sheet (I placed 2 in one so I can have it shown easier), I have the payroll dates.

The second pay period resets and the account rep needs to get over 1000 in gross profit to earn commission.

I am open to all suggestions. I am trying to avoid manually inputting them myself since this is just a small sample of thousands.

First Pay Period = 640
Second Pay Period = 160
Third Pay Period = 0

Here is my example.
 A B C D E A B C D 1 DATE GROSS PROFIT 80% Begin Date 1/1/2015 1/15/2015 1/30/2015 2 1/1/2015 400 320 End Date 1/14/2015 1/29/2015 2/13/2015 3 1/4/2015 500 400 Amount Owed (formula) (formula) (formula) 4 1/7/2015 500 400 5 1/12/2015 400 320 6 1/18/2015 200 160 7 1/22/2015 600 480 8 1/26/2015 100 80 9 1/28/2015 300 240 10 1/31/2015 200 160 11 2/2/2015 100 80 12 2/5/2015 300 240 13 2/8/2015 200 160 14 2/15/2015 500 400

<tbody>
</tbody>

If you have any questions to clear up any confusion, ask away!

Andrew

Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

gaz_chops

Well-known Member
Try this without a helper column

=MAX(0,(SUMIFS(\$B\$2:\$B\$14,\$A\$2:\$A\$14,">="&I1,\$A\$2:\$A\$14,"<="&I2)-1000)*0.8)

Code:
``````[/FONT]
[TABLE="width: 545"]
<colgroup><col span="3"><col span="4"><col><col span="3"></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]GROSS PROFIT[/TD]
[TD="align: right"]80%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Begin Date[/TD]
[TD="align: right"]01/01/15[/TD]
[TD="align: right"]15/01/15[/TD]
[TD="align: right"]30/01/15[/TD]
[/TR]
[TR]
[TD]01/01/15[/TD]
[TD]400[/TD]
[TD="align: right"]320[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Date[/TD]
[TD="align: right"]14/01/15[/TD]
[TD="align: right"]29/01/15[/TD]
[TD="align: right"]13/02/15[/TD]
[/TR]
[TR]
[TD]04/01/15[/TD]
[TD]500[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Amount Owed[/TD]
[TD="align: right"]640[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]07/01/15[/TD]
[TD]500[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/01/15[/TD]
[TD]400[/TD]
[TD="align: right"]320[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18/01/15[/TD]
[TD]200[/TD]
[TD="align: right"]160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22/01/15[/TD]
[TD]600[/TD]
[TD="align: right"]480[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/01/15[/TD]
[TD]100[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28/01/15[/TD]
[TD]300[/TD]
[TD="align: right"]240[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/01/15[/TD]
[TD]200[/TD]
[TD="align: right"]160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/02/15[/TD]
[TD]100[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/02/15[/TD]
[TD]300[/TD]
[TD="align: right"]240[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/02/15[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/02/15[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Lucida Grande]``````

andy_2610

Board Regular
That works!

Is there a way if I want to make the 80% column a formula so it would show 0 for the first 2 dates and the 3rd one it will show 320 and the 4th one 320. Then the second pay period in that column shows is 0,0,0, 160.

gaz_chops

Well-known Member
Not sure if this will be acceptable? I had to add a column with the Week number

=IF(SUMIFS(\$C\$2:C2,\$B\$2:B2,B2)>1000,(SUMIFS(\$C\$2:C2,\$B\$2:B2,B2)-1000)*0.8-E1,0)

Code:
``````[TABLE="width: 231"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]DATE[/TD]
[TD]Week[/TD]
[TD]GROSS PROFIT[/TD]
[TD="align: right"]80%[/TD]
[/TR]
[TR]
[TD]01/01/15[/TD]
[TD]1[/TD]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]04/01/15[/TD]
[TD]1[/TD]
[TD]500[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]07/01/15[/TD]
[TD]1[/TD]
[TD]500[/TD]
[TD="align: right"]320[/TD]
[/TR]
[TR]
[TD]12/01/15[/TD]
[TD]1[/TD]
[TD]400[/TD]
[TD="align: right"]320[/TD]
[/TR]
[TR]
[TD]18/01/15[/TD]
[TD]2[/TD]
[TD]200[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]22/01/15[/TD]
[TD]2[/TD]
[TD]600[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]26/01/15[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]28/01/15[/TD]
[TD]2[/TD]
[TD]300[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD]31/01/15[/TD]
[TD]3[/TD]
[TD]200[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]02/02/15[/TD]
[TD]3[/TD]
[TD]100[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]05/02/15[/TD]
[TD]3[/TD]
[TD]300[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]08/02/15[/TD]
[TD]3[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]15/02/15[/TD]
[TD]4[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]``````

gaz_chops

Well-known Member

Easy way to populate the week numbers if you go with it.

Enter the week numbers above the dates on the right, then in the Column weeks use and drag down.

=INDEX(\$J\$1:\$M\$1,,MATCH(A2,\$J\$2:\$M\$2,1))

Code:
``````[TABLE="width: 681"]
<tbody>[TR]
[TD]DATE[/TD]
[TD]Week[/TD]
[TD]GROSS PROFIT[/TD]
[TD="align: right"]80%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Week[/TD]
[TD="align: right"][B][COLOR=#ff0000]1[/COLOR][/B][/TD]
[TD="align: right"][B][COLOR=#ff0000]2[/COLOR][/B][/TD]
[TD="align: right"][B][COLOR=#ff0000]3[/COLOR][/B][/TD]
[TD="align: right"][B][COLOR=#ff0000]4[/COLOR][/B][/TD]
[/TR]
[TR]
[TD]01/01/15[/TD]
[TD]1[/TD]
[TD]400[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Begin Date[/TD]
[TD="align: right"]01/01/15[/TD]
[TD="align: right"]15/01/15[/TD]
[TD="align: right"]30/01/15[/TD]
[TD="align: right"]14/02/15[/TD]
[/TR]
[TR]
[TD]04/01/15[/TD]
[TD]1[/TD]
[TD]500[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End Date[/TD]
[TD="align: right"]14/01/15[/TD]
[TD="align: right"]29/01/15[/TD]
[TD="align: right"]13/02/15[/TD]
[TD="align: right"]27/02/15[/TD]
[/TR]
[TR]
[TD]07/01/15[/TD]
[TD]1[/TD]
[TD]500[/TD]
[TD="align: right"]320[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Amount Owed[/TD]
[TD="align: right"]640[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/01/15[/TD]
[TD]1[/TD]
[TD]400[/TD]
[TD="align: right"]320[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18/01/15[/TD]
[TD]2[/TD]
[TD]200[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22/01/15[/TD]
[TD]2[/TD]
[TD]600[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/01/15[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28/01/15[/TD]
[TD]2[/TD]
[TD]300[/TD]
[TD="align: right"]160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/01/15[/TD]
[TD]3[/TD]
[TD]200[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/02/15[/TD]
[TD]3[/TD]
[TD]100[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/02/15[/TD]
[TD]3[/TD]
[TD]300[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08/02/15[/TD]
[TD]3[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/02/15[/TD]
[TD]4[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]``````

andy_2610

Board Regular
That should do it. I'll just add a formula to the week column telling it if the date is between certain dates denotes a week #.

Thanks!

gaz_chops

Well-known Member
You're welcome, check my last post re week numbers.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,164
Messages
5,835,753
Members
430,384
Latest member
kiuwai

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back