Adding Unique Cost of Items in a sheet of form results

Rob Turriziani

G'Day All,

I've tried to search, but come up empty handed!

I'm building a stock control system that suits our business, and trying to work out the cost of an item (Items are in Columns) adding each time it was bought (Buy price may vary)

Each row will be a form response, adding a quantity of the item bought, and the value bought for.

I want to do a formula that gives me the sum of - the Quantity of items bought * the buy price - each time it was bought (buy price can vary)

Any help would be most greatly appreciated!

Andrée Starå

Hi @Rob Turriziani

I hope you're well and safe!

I'd be happy to take a quick look.

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Rob Turriziani

Hi @Andrée Starå Thank you so much for your fast reply!

I'll try and explain my process in a little more detail, I think the screenshot included in my original post is about all i can share (nothing else much exists at this time!)

The process would be as follows:

1) Company purchase order is created with line items.

2) We open the form I created linked to this sheet which asks the information from the Purchase order (P.O)

3) We fill the form with the details (ONE FORM = 1 P.O LINE ITEM = 1 ROW in the sheet)

4) The items to fill are: Ordering Person Details, P.O Number, Cost Per Unit ordered, Quantity of Item Ordered etc.

4A) This form is repeated for every line item on the P.O

The very top row is a simple "sum(Column:Column)" to give me the total units ordered in. (I then cell link this to a summary sheet for other calculations)

The second row I would like a "Running Total Cost" formula... this is what I'm struggling with.

I need the formula to do the following:

Take the number of items ordered on that row.

Multiply that quantity purchased by the cost as per that Purchase order, and then add that total to the running total cost of that item (in Cell 2 of that column)

Issue to note is that the item cost will vary each time it is purchased.

We also currently have 59 "items" which I have done as 59 COLUMNS to allow us to use FORM to add a new data set of Purchase information

I have added a couple of columns to work-around the Logic Limits in the forms (To allow me to only show that customer's Parts when filling the form) and there are some hidden columns in the attached screen shot which are not important to this question - they are just stock location questions.

Screen Shot Below shows:

The light grey row (Row 2) is where i would like the "TOTAL ITEM COST"

The Item Cost is in Column 4 "Actual Cost - Total of all inputs - Pet Item"

Quantity of the item ordered is in the right hand columns (of which there are 59 columns all up currently!!)

I hope this helps, and thank you again for your assistance!

Rob Turriziani

I've Created a work around!!

I added a column to create the cost for that particular row (Unit cost x quantity) and then a Sumif to add that value as a column total

I think this will get me across the line!

Andrée Starå

@Rob Turriziani

Excellent!

You're more than welcome!

This might be something that could help.

I developed a solution using two forms (or the same form by using conditional logic) that can be used to submit the main information and then the other information on a new row for each submission after the first.

Would that help?

Rob Turriziani

@Andrée Starå

Thanks for that!

I've actually created exactly the same in my form.

A form that collects the main info, and then based on client - reveals the "client items" fields (Columns) that then collects the Quantity ordered.

What i could not reference was then "Total Cost Value" meaning - 10 pcs x $50.00 Cost = $500 for this line. that would then total to the "Total value of all purchases for this item" (Because cost value is not consistent!

I created a column that Added THAT ROW (Revealing the $500 cost for THAT ENTRY), and then used a SUMIF on the column (I did this on another sheet to allow me to use a whole-column reference so that when the form adds a row of data - the formula automatically "expands" to take in this new row!)

Thanks again for your kind offer to assist, I think I ended up working my way to your solution anyhow!

Kind Regards

Rob.

Andrée Starå

@Rob Turriziani

Excellent!

Happy to help!