2

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

player0
  • 124,011
  • 12
  • 67
  • 124
Anatoly
  • 5,056
  • 9
  • 62
  • 136

2 Answers2

2

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • @Anatoly https://docs.google.com/spreadsheets/d/1fgVdxe19avxCwEunkrAlV2KTdRnvaYtPV2XpmNM_kYU/edit#gid=0 – player0 Aug 18 '20 at 18:57
  • sorry but the second answer is much shorter and easy to understand/reuse – Anatoly Aug 18 '20 at 21:23
  • @Anatoly note that on 2nd answer items in F column needs to be in the same order as items on row 1 - otherwise it won't work – player0 Aug 18 '20 at 21:38
  • it's the same order because I create it by using `TRANSPOSE` formula from the row. In any case my respect, it looks very scary what you have done. Will show my coworkers when they will complain that TypeScript is difficult language :) – Anatoly Aug 18 '20 at 21:41
  • 1
    @Anatoly `--C2:E` is equivalent to `1*C2:E` or `N(C2:E)` - reason is that MMULT works only with numbers so empty cells needs to be converted into zeros – player0 Aug 18 '20 at 21:53
  • Thank you. What does `INDEX` do here, it returns offset by providing 3 parameters according to doc, no? – Anatoly Aug 19 '20 at 08:23
  • 1
    INDEX is type of ARRAYFORMULA - no benefits, its just shorter to type – player0 Aug 19 '20 at 09:16
1

For a alternative solution, try:

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

enter image description here

JPV
  • 26,499
  • 4
  • 33
  • 48
  • 1
    Can you explain please what does it mean `--C2:E` it's single part which I can't find by googling – Anatoly Aug 18 '20 at 21:24
  • 1
    The double minus, also known as double unary, is an operation to convert Boolean values into ones and zeroes. Simply; TRUE to one (1) and FALSE to zero (0). As MMULT expects numeric values, it will return an error if any of the cells are empty. The double unary used here fills those empty cells with a zero. – JPV Aug 19 '20 at 06:21