I have several sheets that are price listings. My goal is when customers enter a quantity in column F for the item they want to purchase, the corresponding row with all the info for that item (item #, description, price, quantity and total cost) will copy to another sheet titled Order Summary where they will fill out billing and shipping info and see all the items they are ordering from the various pricing sheets.

This allows the customer to submit one sheet rather than the entire pricing guide. I then want it to total all of the copied rows to show the total price for their purchase. How to set up this type of functionality in Google Sheets?


One way to achieve this is to put something like this on the Order Summary sheet:

={ filter(Sheet1!A2:G, len(Sheet1!F2:F)); filter(Sheet2!A2:G, len(Sheet2!F2:F)) }

This does the following: for each listed sheet (here Sheet1 and Sheet2) takes all entries in columns A:G where the cell in column F is not blank. You can have more semicolon-separated filter commands here, to summarize more sheets.

To have the final total on Order Summary, just use the sum function on the relevant column: e.g., =sum(G2:G). This formula would be placed aside from the range of filter, so the output of filter does not overlap it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.