Sumproduct combined with a condition of subject to maximum

[x0nar]

Suppose I have the following data

[TABLE="width: 500"]
<tbody>[TR]
[TD]Alex[/TD]
[TD]01/07/18[/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]09/07/18[/TD]
[TD]20000[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]13/07/18[/TD]
[TD]35000[/TD]
[/TR]
[TR]
[TD]Jason[/TD]
[TD]13/11/18[/TD]
[TD]26000[/TD]
[/TR]
</tbody>[/TABLE]

Now, I'd like to find the maximum commission earned by a person for a month subject to the condition that for each transaction max commission earned cannot exceed 25000

Now consider an Employee Alex. Let's say we want to calculate commission earned by him for the month of July.
As we can see the commission earned by him would be 10000 + 25000 = 35000

Now how do I derive the answer without adding any helper row / column using just a single formula.

As far as I know either the sumproduct or sum with array function can be used and using both I am able to derive a formula except that it does not consider the max commission limit. Somehow I cannot get my head around the MIN Function part.

Appreciate your help.

 

[jtakw]

Hi,

Does this help:

Book1
	A	B	C	D	E	F	G
1	Alex	7/1/2018	10000		Month to Sum	Salesperson	Result
2	Mike	7/9/2018	20000		7	Alex	25000
3	Alex	7/13/2018	35000		7	Mike	20000
4	Jason	11/13/2018	26000		7	Jason	25000
Sheet304

Cell Formulas
Range	Formula
G2	=MIN(25000,SUMPRODUCT((A$1:A$4=F2)*(MONTH(B$1:B$4=E2)*C$1:C$4)))

 

[x0nar]

Hi,

Does this help:

	A	B	C	D	E	F	G
Alex	Month to Sum	Salesperson	Result
Mike	Alex
Alex	Mike
Jason	Jason

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

[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7/9/2018[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]25000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]7/13/2018[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]20000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]11/13/2018[/TD]
[TD="align: right"]26000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]25000[/TD]

</tbody>

Sheet304​

[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] "]G2[/TH]
[TD="align: left"]=MIN(25000,SUMPRODUCT((A$1:A$4=F2)*(MONTH(B$1:B$4=E2)*C$1:C$4)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi. Sorry for the late reply. I guess you misinterpreted my requirement. What I would prefer the function to perform is to cap the commission amount to $25000 per entry, and not month-wise.
So for instance if we consider the case of Alex, there appear to be two entries ie. on 1st July and 13th July. Now the commission on 1st July does not breach the cap,hence it would be considered in its entirety of $10000. However with regards to the commission on 13th July of $35000, it exceeds the $25000 Cap, hence only $25000 will be considered. So, Alex's commission for the month of July would amount to $35000.
What I believe your code does is it considers a cap value by the end of month resulting in an answer of $25000.

Any fix to this. Thank you for your reply

 

[kvsrinivasamurthy]

Data in B2:D2
J2=Alex
K2=7
ARRAY formula In L2

=SUM(($B$2:$B$5=J2)*(MONTH($C$2:$C$5)=K2)*(IF($D$2:$D$5>25000,25000,$D$2:$D$5)))

How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

 

[x0nar]

Data in B2:D2
J2=Alex
K2=7
ARRAY formula In L2

=SUM(($B$2:$B$5=J2)*(MONTH($C$2:$C$5)=K2)*(IF($D$2:$D$5>25000,25000,$D$2:$D$5)))

How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

Hi @kvsrinivasamurthy. Thanks for your reply. It worked.
Somehow I am unable to get my head around this array part in Excel. I mostly have trouble understanding if a command would get inside an array or otherwise.
I would really appreciate if you could redirect me to a source/website/book which could help me understand the properties of Arrays in Excel and their collaboration with functions like SUM,SUMPRODUCT etc. I mean the way you figured it out, there must be some inherent logic to derive how to get commands inside an array.

Thanks a lot.

 

[kvsrinivasamurthy]

To understand ARRAY formulas
link