Formula to sum list with simular SKU

[foxtrapperscott]

I looking for a formula that will search a list of SKU that are similar and sum the sales totals in a cell with the Primary SKU. So a formula that looks for S650 in the data and gives the sum of all with similar SKU as the result. The example may be more helpful below to see what I'm trying to accomplish. Thanks for your help, I greatly appreciate it!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DATA[/TD]
[TD]SALES[/TD]
[TD][/TD]
[TD][/TD]
[TD]COMBINED SKU[/TD]
[TD]TOTAL SALES[/TD]
[/TR]
[TR]
[TD]S650[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]S650[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]S650 IT4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]S750[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]S650 T4[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD]S850[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]S750[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S850[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S850 T4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[igold]

In your example if "Data" is in cell A1 then how about this copied down starting in cell F2 (TOTAL SALES)

=SUMIFS(B2:B7,A2:A7, "*" & E2 & "*")

 

[UniMord]

Let's create tables for each of these, it'll simplify matters. Select the SKU table and hit CTRL<ctrl>+T.
Select the Totals Table and hit CTRL<ctrl>+T again. Now you have 2 tables. If you don't rename them, they'll automatically be called Table1 and Table2.

Now, you can enter the following formula in the TOTAL SALES column: =SUMIF(Table1[DATA], [@[COMBINED SKU]] & "*", Table1[SALES])</ctrl></ctrl>

 

[foxtrapperscott]

Thank you so much! It works great!

 

[igold]

Not sure which solution you are using, but either way I am happy you got it working. Thanks for the feedback.

 

[foxtrapperscott]

I have ran into a bump in the road with the formula that "igold" shared. The scenario changed when I had an SKU that was "E32I" and a different SKU, but similar "E32". It is currently giving me the same result for both SKU's. How can I change the formula to recognize the difference in SKU?
Thanks for taking time to help!

 

[UniMord]

I recommend that you don't prepend the wildcard, but use the formula I suggested above. Then, the Total Sales will match whatever starts with the text you put in the COMBINED SKU column.
If you put in E32, it will match E32, E32I, etc., if you put in E32I, it will match only items that begin with that.

 

[foxtrapperscott]

Okay, I'll give it a try. Thanks!

 

[igold]

@UniMord

I tried your suggestion and the SKU E32 in the Combined SKU table picks up the totals for both E32 and E32I.

 

[UniMord]

It seems that you want everything that comes after a space to be ignored, but the SKU before the space to be significant. If that's correct, then, we should use the following formula:

=SUMPRODUCT((LEFT(Table1[DATA], FIND(" ", Table1[DATA] & " ")-1)=[@[COMBINED SKU]])*Table1[SALES])