Sum Criterias

A

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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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]
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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]
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
176
harzer
H
L
  • Question
Excel finance task
Replies
2
Views
322
Fluff
Fluff
J
  • Solved
Subtotals within Sumifs range
Replies
6
Views
215
jaz44
J
MrDB4Excel
  • Question
Refund or Payment
Replies
2
Views
347
MrDB4Excel
MrDB4Excel
A
  • Question
Calculate Optimum Duration in Loan
Replies
1
Views
317
anuradhagrewal
A

Forum statistics

Threads
1,214,622
Messages
6,120,585
Members
448,972
Latest member
Shantanu2024

Share this page

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
Back
Top