Sum Criterias

andy_2610

Board Regular
Joined
Jan 29, 2015
Messages
168
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.
ABCDEABCD
1DATEGROSS PROFIT80%Begin Date1/1/20151/15/20151/30/2015
21/1/2015400320End Date1/14/20151/29/20152/13/2015
31/4/2015500400Amount Owed(formula)(formula)(formula)
41/7/2015500400
51/12/2015400320
61/18/2015200160
71/22/2015600480
81/26/201510080
91/28/2015300240
101/31/2015200160
112/2/201510080
122/5/2015300240
132/8/2015200160
142/15/2015500400

<tbody>
</tbody>


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

Thanks in advance

Andrew
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
Joined
Jan 29, 2015
Messages
168
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Jan 29, 2015
Messages
168
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,217
Messages
5,594,887
Members
413,947
Latest member
gizmolucy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top