I am trying to build a spreadsheet to record sales for a Scout fundraiser. I would like to have it be able to calculate the amount each Scout needs to turn in. I have the following spreadsheet set up:

https://docs.google.com/spreadsheets/d/1Knh6zNSdP4Vie-vfGGAsVS3TgiXdKPQBvz3rHD0Q2pA/edit?usp=sharing

There is a column for the total due, followed by columns for all of the different products, where a quantity is entered:

I also have a lookup table in a separate sheet for the prices of each item:

The formula I have right now (to get the total for a single column) is:

=VLOOKUP($E$1, Sheet3!A1:B50, 2, false) * E2

I am trying to figure out how to get the total for each row without copying the LOOKUP*QTY formula once for each column (since there are 50 products). Is there a way to accomplish this with Google Sheets?

CodePudding user response：

Went back to it after posting and found what I think is the solution about two minutes later after my continued googling brought me to ARRAYFORMULA:

=ARRAYFORMULA(SUM(VLOOKUP($E$1:$BB$1, Sheet3!$A$1:$B$50, 2, false) * E2:BB2))

This looks like it works, but going to test it out further to be sure.

CodePudding user response：

Since your items in 'Final Sales Numbers'!D1:BA1 are an exact match, in order, for the items in 'Price Lookups'!A1:A50, you really don't need to use VLOOKUP at all.

If you like, delete 'Final Sales Numbers'!C:C (including the header) and place the following formula in C1:

=ArrayFormula({"Total Due";IF(A2:A="",,MMULT(D2:BA*TRANSPOSE('Price Lookups'!B1:B50),SEQUENCE(50,1,1,0)))})

This will produce the header and all results (one form responses begin to come in, of course).

You'll see that whatever is in the 50 columns of 'Final Sales Numbers'!D2:BA can be directly multiplied by the pricing in 'Price Lookups'!B2:B50. MMULT then takes those numbers as Matrix 1 and multiplies them by a Matrix 2 made of a stack of 50 1s. Because matrix multiplication is really a two-step process involving multiplying all rows in one matrix by all columns of another and then adding those results, this will give you a row-by-row sum.