Get sum based on part of cell value

[Junarkar]

Hi

The heading doesn't totally describe my actual problem. I have below data;

			Emp_ID	Emp_ID
Article	Article Code	Incentive	12345	123456
Articlexxx	2233200/2233201/2233202	50

I am trying to get the incentive based on the qty. sold for each "Article Code".

Example;
If Emp_ID 12345 sold 1 qty of Article 2233200, then he earned incentive of 1x50 = 50
And he has also sold 2 qty of Article 2233201 , then he earned incentive of 2x50 = 100

So his total incentive for Articlexxx will be 150.

For one article I can split it up to multiple rows manually but in reality this list is huge and manually doing it is hectic.

Below is the sample sales Data from where I will get qty sold;

Emp_ID	Article	Article Code	Qty Sold
12345​	Article xxx	2233200	1​
12345​	Article xxx	2233201	2​

For one article I used "Sumifs" function but I have no idea not to take part of each part from a cell and do the search and then combine all the search together as one result.

Please share your thoughts.

Thanks alot

 

[bosco_yip]

Try................

In D3 formula copied right to E3 :

=SUMPRODUCT(($A$8:$A$11=D$2)*($B$8:$B$11=$A3)*ISNUMBER(FIND($C$8:$C$11,$B3))*$D8:$D11)*$C3

 

[Junarkar]

Try................

In D3 formula copied right to E3 :

=SUMPRODUCT(($A$8:$A$11=D$2)*($B$8:$B$11=$A3)*ISNUMBER(FIND($C$8:$C$11,$B3))*$D8:$D11)*$C3

View attachment 67563

Thanks alot.. I have tried this and got #value error. I made a small change that I've removed ($B$8:$B$11=$A3) portion. Then I checked with Evaluate Formula option and found that the error is happening on the last step;

Example - For Emp_ID 12345, the formula goes upto - sumproduct({1;1;0}*$D8:$D11)*$C3 without any errors.

And then next step returns this error - sumproduct({#VALUE!;#VALUE;#VALUE})*$C3

I tried it as CSE formula but returned error. I also tried by using your formula without any change but that also returned the same error.

I made sure all numbers are in number format itself.

Where I did the mistake.

Regards
Junarkar

 

[Peter Bartholomew]

I would be tempted to add a column to the sales table.



Using Office 365, the formula could be
= [@[Qty Sold]] *
XLOOKUP( TRUE,
ISNUMBER( FIND([@[Article Code]], IncentiveTbl[Article Code]) ),
IncentiveTbl[Incentive] )

A more exotic version, using beta release versions of Excel, might be
= LET(
Located?, ISNUMBER( FIND([@[Article Code]], IncentiveTbl[Article Code]) ),
ArticleIncentive, XLOOKUP( TRUE, Located?, IncentiveTbl[Incentive] ),
Incentive, [@[Qty Sold]] * ArticleIncentive,
Incentive )

The incentive payment by employee would then be a simple SUMIFS.

p.s. From Excel 2007 on, I think
= [@[Qty Sold]] *
LOOKUP( 1,
1 / ISNUMBER( FIND([@[Article Code]], IncentiveTbl[Article Code]) ),
IncentiveTbl[Incentive] )
will work.

 

[bosco_yip]

Thanks alot.. I have tried this and got #value error. I made a small change that I've removed ($B$8:$B$11=$A3) portion. Then I checked with Evaluate Formula option and found that the error is happening on the last step;
Example - For Emp_ID 12345, the formula goes upto - sumproduct({1;1;0}*$D8:$D11)*$C3 without any errors.
And then next step returns this error - sumproduct({#VALUE!;#VALUE;#VALUE})*$C3

1] If you made change in remove ($B$8:$B$11=$A3) portion, then the formula become >>

=SUMPRODUCT(($A$8:$A$11=D$2)*ISNUMBER(FIND($C$8:$C$11,$B3))*$D8:$D11)*$C3

>>

=SUMPRODUCT({1;1;0;0}*$D8:$D11)*$C3 ==>> the Sumproduct range using 4 x 4 cells

>>

=150

2] Your checking formula goes to >>

sumproduct({1;1;0}*$D8:$D11)*$C3 ==>> the Sumproduct range using 3 x 4 cells

of course giving error !

Regards

 

[Junarkar]

I would be tempted to add a column to the sales table.

View attachment 67676

Using Office 365, the formula could be
= [@[Qty Sold]] *
XLOOKUP( TRUE,
ISNUMBER( FIND([@[Article Code]], IncentiveTbl[Article Code]) ),
IncentiveTbl[Incentive] )

A more exotic version, using beta release versions of Excel, might be
= LET(
Located?, ISNUMBER( FIND([@[Article Code]], IncentiveTbl[Article Code]) ),
ArticleIncentive, XLOOKUP( TRUE, Located?, IncentiveTbl[Incentive] ),
Incentive, [@[Qty Sold]] * ArticleIncentive,
Incentive )

The incentive payment by employee would then be a simple SUMIFS.

p.s. From Excel 2007 on, I think
= [@[Qty Sold]] *
LOOKUP( 1,
1 / ISNUMBER( FIND([@[Article Code]], IncentiveTbl[Article Code]) ),
IncentiveTbl[Incentive] )
will work.

Hi,

Thanks alot for the solution. I'm using Excel 2017 so Xlookup and LET are not available.

Also now the data format has been slightly changed and I'm attaching the sample file here. I tried your last formula but gives error. Because

in this portion of the formula FIND([@[Article Code]], IncentiveTbl[Article Code]), IncentiveTbl[Article Code] is not a single cell but a range.

When I tried it with single cell its working fine and with a range gives error.

I request you to kindly look into my attached file.

Regards
Junarkar