Google Spreadsheet/Excel formula for calculating shipping expenses of my orders

I have Website form that is entering data in Google Spreadsheet and I need to calculate automatically by Google Spreadsheet formula Shipping Cost for all orders. Data consists of:

1. Name
2. Email
3. Online Store
4. Subcategory
5. Price
6. Online Store 2
7. Subcategory 2
8. Price 2
9. Online Store 3
10. Subcategory 3
11. Price 3

Here is how it looks like in Google Spreadsheet: Input data from Website Form

When client submit inquiry on Website form data is entered in Google Spreadsheet and I would like to automatically calculate shipping cost so that I can send automatic email to client with shipping cost for products he is interested in.

All shipping prices and minimum order value for FREE shipping is located on the 2nd Google Spreadsheet and it looks like this: Free shipping & Shipping Cost

Example for JOHN COLTRANE: John wants to buy 2 Footwear + 1 Bra from Nike.com. As all 3 products are from Nike.com and in total they are above minimum order for FREE shipping of $150 he will not pay anything for shipping cost for this order. I need formula that will sum orders from same online store and check if that orders total from same store is above or below minimum order for FREE shipping and if it's below then to calculate how much will client pay for shipping cost.

Example for client MILES DAVIS: Miles wants 1 pcs Bra from VictoriasSecret.com (VS) priced $58 and Footwear from Nike.com priced $125. Minimum FREE shipping for VS is order above $250 so he needs to pay $6.99 for VS shipping cost + Nike minimum FREE shipping is $150 so he needs to pay additionally $8 for shipping cost as well. In total Miles Davis needs to pay $14.99 for shipping cost of both products.

I hope I have explained well what kind of formula I need for this. My guess based on previous experiences and Googling solution is that it could be some ArrayForm + LookUP + SUMIFS or PRODUCTSUM combination. I can break up additionally this formula to calculate partially in different cells as long as I get it calculate shipping cost at the end. This formula need to be added automatically when new row is entered by Website form.

Thank you so much for your help as I'm desperately looking to solve this for almost a week so far. Let me know if you have any additional questions that I can address.

Recent Questions...