google-sheetssumgoogle-sheets-formulaarray-formulasgs-vlookup

Formula to calculate sum of items in google spreadsheet


I have items in a row (item 1, item 2, etc...) and prices for every item in a column, can I write a sophisticated formula which will calculate the total for every person, without manually entering all intersection?

| Name   | Total   | item 1 | item 2 | item 3 |                 |    |
|--------|---------|--------|--------|--------|-----------------|----|
| John   | 2+9+2=13| 1      | 3      | 2      | price of item 1 | $2 |
| Daniel | 0+6+1=7 | 0      | 2      | 1      | price of item 2 | $3 |
| Max    | 2+3+0=5 | 1      | 1      | 0      | price of item 3 | $1 |

Link to Google Spreadsheet with the example

Unfortunately, I can't use scripting here, otherwise, it would be a peace of cake :(


Solution

  • For a alternative solution, try:

    =ArrayFormula(if(len(A2:A), mmult(--C2:E, G2:G4),))
    

    enter image description here