Commissions formula - IF/THEN

[OlinAct]

We are trying to create a single column for double checking commissions pays. All the data is on one worksheet

The logic would look something like:

'If there are 1-14 lines with the dates of October, then multiply the value in this cell by .01, and if there are 15-19 lines with the dates of October, then multiply the value in this cell by .0125, and if there are over 20 lines with the dates of October, then multiply the values in this cell by .015.'

 

[Dave Patton]

An example would have been useful.
Check your definition of criteria.

Excel 2010
	A	B	C	D	E	F	G
1	Date				1-Oct-18	21	0.015
2	9-Sep-18				31-Oct-18
3	9-Sep-18
4	1-Oct-18
5b

Cell Formulas
Range	Formula
F1	=COUNTIFS(A:A,">="&E1,A:A,"<="&E2)
G1	=IF(F1>20,0.015,IF(F1>15,0.0125,IF(F1>0,0.01,0)))
E2	=EOMONTH(E1,0)

 

[OlinAct]

An example would have been useful.
Check your definition of criteria.

Excel 2010

	A	B	C	D	E	F	G
Date

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Oct-18[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]0.015[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]9-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]31-Oct-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]9-Sep-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1-Oct-18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

5b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]=COUNTIFS(A:A,">="&E1,A:A,"<="&E2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]=IF(F1>20,0.015,IF(F1>15,0.0125,IF(F1>0,0.01,0)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=EOMONTH(E1,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Got it. An example, meaning what data? Say I have 15 lines, all different October dates, with a column that has a sales number. I need to create a column that calculates the percentage of the sales number, dependent on how many lines there are with October dates.

So say Column E has a date, column F is the sale amount, and column G will be the commission amount, calculated off column F and how many lines there are that have October dates.

 

[ratchet1369]

To save space, I'd build a short table for the commissions and eliminate the IF statement. The IF is cleaner and requires less resources but would be a little wordy if you're aiming for one formula.

=vlookup(dave's countIFS, commissions table, 1 Fuzzy Match)* sumifs (same formula as Dave's CountIFS)

If you're looking to do this per line, instead of a summary like Dave is showing, drop the Sumifs and multiply by the Sales Amount on that line.