Need SKU to represent a $$ Amount

[iicanceii]

My goal is to see what SKU sold and associate the COST to it.



I have an excel document for orders we receive and the SKUs vary depending on what the customer purchases.

An example of one sku would be: XBONECC-RP-X1spRP-X1Splat-X1iS2ZHbkp-X1iS2ZHs-X1xMM-X1Pp-X1mRd-X1mPRtg-X1mRGb


I want to grab elements like "-XBONECC-" and make that populate a cell with $17. Then if the SKU also contains "-X1xMM-" then the value would be $40 in another cell.
Note: The SKU dynamically changes, so the location of the -X1xMM- for example can vary where it falls within the full SKU line.


Ideally the cells would show the value of that sku, then we would just do a =sum to get the total cost of the SKU.


Any help is greatly appreciated, thank you so much! Can't find anything related to this need.

 

[FDibbins]

You could use something like this...

	A​	B​	C​
1​	XBONECC-RP-X1spRP-X1Splat-X1iS2ZHbkp-X1iS2ZHs-X1xMM-X1Pp-X1mRd-X1mPRtg-X1mRGb	0​	40​

B1=IF(ISNUMBER(SEARCH("-XBONECC-",$A1,1)),17,0)
(Note that your string does not start with - so it is not found. Remove the - and it works)
C1=IF(ISNUMBER(SEARCH("-X1xMM-",$A1,1)),40,0)
etc

 

[iicanceii]

Ford, I appreciate your prompt response and solution!

My one question, my SKUs dynamically change so sometimes things are left out. I believe your solution is still a remedy, but wanted to clarify.

If I performed that action on the ones below, it still correctly applies?
XBONECC-RP-X1spRP-X1Splat-X1iS2ZHbkp-X1iS2ZHs-X1xMM-X1Pp-X1mRd-X1mPRtg-X1mRGb
XBONECC-RP-X1spRP-X1Splat-X1iS2ZHbkp
XBONECC-X1iS2ZHs-X1xMM-X1Pp-X1mRd-X1mPRtg-X1mRGb

1. I imagine if I wanted to associate a $ to any of the skus, I would build a full specification list and if some SKUs didn't exist it would just put 0 or not account for it at all.

 

[iicanceii]

Disregard my previous message.

What if I wanted to have it add everything following my specs into One Cell?
=IF(ISNUMBER(SEARCH("XBONECC",$A1,1)),17,0)
=IF(ISNUMBER(SEARCH("-X1xMM-",$A1,1)),40,0)

ie SKU is XBONECC-RP-X1xMM then I would have cell B1=$57

 

[FDibbins]

If you just need to combine them (if either or both exist)...
=IF(ISNUMBER(SEARCH("XBONECC",$A1,1)),17,0)+IF(ISNUMBER(SEARCH("-X1xMM-",$A1,1)),40,0)

 

[iicanceii]

Thx

 

[iicanceii]

Ford,

I understand your formula fully, however I have instances when IF BOTH SKUS FOUND = X, rather than this/or
=IF(ISNUMBER(SEARCH("XBONECC",$A1,1)),17,0)+IF(ISNUMBER(SEARCH("-X1xMM-",$A1,1)),40,0) your version of this/or

What do I do if I wanted the equation above to equal$40 only?

 

[FDibbins]

I understand your formula fully, however I have instances when IF BOTH SKUS FOUND = X, rather than this/or

I don't understand?
My formula says that IF a certain text is found, use 17, otherwise use 0 PLUS IF another certain text is found, use 40, otherwise 0

So if the 1st text is found it will give you 17
if the 2nd text is found, it will give you 40
If they are both found, it will give you 57
If neither is found, it will give you 0

Can you give a sample of your latest version?

 

[iicanceii]

So if the 1st text is found it will give you 17
if the 2nd text is found, it will give you 40
If they are both found, it will give you 57
If neither is found, it will give you 0

This is correct, however sometimes we have products that if the user purchased "2" add-ons, the COST does NOT increase. The example above is when its either one, but now say XBONECC cost $40 individually and X1xM cost $40 individually. However, this customer bought BOTH of the SKUs and therefore our cost is only $40, not $80. This is because both SKUs have the same hardware, just different coding. So no extra cost if the person already paid for one of the 2 add-ons.

Let me know if that's unclear, thanks!

 

[iicanceii]

This may be more clear-
1 SKU = MM
2 SKU = PSMM

The consumer buys product and gets string "MM" only. This = $10
The consumer buys product and gets string "PSMM" only. This = $10
The consumer buys product and gets string "MM-PSMM" both = $10

The current formula you gave me will see both SKUS and make it = $20, which is wrong because there's no extra cost if both of those SKUs come up in the string.