CODEWITHSUNDEEP
google-sheetssumgoogle-sheets-formulaarray-formulasgs-vlookup
Anatoly
Anatoly

Reputation: 5243

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 :(

Upvotes: 2

Views: 160

Answers (2)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(A2:A="",,
 MMULT(HLOOKUP(C1:E1, TRANSPOSE(G2:H), 2, 0)*C2:E, 
 ROW(INDIRECT("A1:A"&COLUMNS(C:E)))^0)))

enter image description here

enter image description here

or shorter:

=INDEX(IF(A2:A="",,MMULT(1*C2:E, G2:G4)))

enter image description here

Upvotes: 2

JPV
JPV

Reputation: 27262

For a alternative solution, try:

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

enter image description here

Upvotes: 1

Related Questions