Hey all, I'm evaluating Smartsheets for my business and trying to determine whether workarounds exist for some necessary calculations. Three big ones are cost calculation, weight calculation and inventory management. I currently use Excel for these. My arrangement is I have all our products on the top row, with all of our parts to make those products in the left column (see attachment). For cost & weight, I use SUMPRODUCT vertically. From what I can tell, the best workaround is to create extra columns for each product that calculate total cost and weight of each line item, and then sum at the bottom. Cumbersome workaround (because we have 100 products), but maybe not a dealbreaker. So first question is - does anyone know a better workaround?
The second question is on inventory management, and this is the one I haven't discovered any workaround for as of yet. I take the quantities of each product sold from this sheet:
Then I do a SUMPRODUCT horizontally with this sheet and the first sheet to produce a current inventory of all the parts (see below)
The Excel formula then for cell C3 in Sheet3 is "=B3-SUMPRODUCT(Sheet2!B$2:K$2, Sheet1!D3:M3)"
Is there a reasonable way to replicate this calculation in Smartsheets?
Help Article Resources
Check out the Formula Handbook template!