google-sheets google-sheets-formula spreadsheet sumproduct

Googlesheets SUMPRODUCT with position

PORTION	NETO	PROPORTION
db 05;db 34	34140	0,6;0,4
db 05	10000	1
db 03;db 04;db 05	10000	0,7;0,1;0,2
db 04;db 05	5000	0,4;0,6

I have a sample file like the above table.

I need a =SUMPRODUCT(A2:A="db 05";B2:B;C2:C) but need some kind of SPLIT with POSITON.

Example 1: first line "db 05" is in position 1, so I should get 34140*0,6 = 20484 where 0,6 is in first position.

Example 2: 2nd line is just "db 05" and a simple =SUMPRODUCT(A2:A="db 05";B2:B;C2:C) should work just fine or just leave it empty and ignore Column C should be easier.

Example 3: 3rd line "db 05" is in position 3, so I should get 10000*0,2 = 2000.

Ideas I've tried:

=SUMPRODUCT(
  (ISNUMBER(SEARCH("db 05"; A2:A))) * 
  B2:B * 
  IF(
    C2:C <> ""; 
    INDEX(SPLIT(C2; ";"); MATCH(TRUE; ISNUMBER(SEARCH("db 05"; SPLIT(A2; ";"))); 0)); 
    1
  )
)

and

=SUMPRODUCT(
  (ISNUMBER(SEARCH("db 05"; A2:A))) * 
  B2:B * 
  IFERROR(
    INDEX(SPLIT(C2:C; ";"); MATCH("db 05"; SPLIT(A2:A; ";"); 0));
    1
  )
)

But I can't get it to match the position correctly for every line.

Solution

Here's one approach (add formula in D2):

=MAP(A2:A5,B2:B5,C2:C5, 
  LAMBDA(portion,neto,proportion, 
    SUBSTITUTE(
      INDEX(
        SPLIT(proportion,";"), 
        MATCH("db 05",SPLIT(portion,";"),0)
      ), 
      ",","."
    )*neto
  )
)

The basis being:

=SUBSTITUTE(
  INDEX(
    SPLIT(C2,";"), 
    MATCH("db 05",SPLIT(A2,";"),0)
  ), 
  ",","."
)*B2

Result (formula added in D2):

PORTION	NETO	PROPORTION	RESULT
db 05;db 34	34140	0,6;0,4	20484
db 05	10000	1	10000
db 03;db 04;db 05	10000	0,7;0,1;0,2	2000
db 04;db 05	5000	0,4;0,6	3000

Explanation / Intermediates

Use SPLIT on "portion" to find the MATCH for "db 05".

=MATCH("db 05",SPLIT(A2,";"),0)
// 1 (we need first multiplier)

Use inside INDEX with a SPLIT on "proportion".

=INDEX(SPLIT(C2,";"),1)
// 0,6

Result will be the correct multiplier. E.g., "0,6" for the first row. Use SUBSTITUTE to turn that into "0.6". (Whether this is necessary may depend on your locale.)
Finally, multiply by B2 ("neto") and use MAP to apply to each row.

A SUMPRODUCT solution could be as follows (add formula in D2):

=MAP(A2:A5,B2:B5,C2:C5, 
  LAMBDA(portion,neto,proportion, 
    SUMPRODUCT(
      (SPLIT(portion,";")="db 05")* 
      SUBSTITUTE(
        SPLIT(proportion,";"), 
        ",","."
      )*neto
    )
  )
)

But you only need that if you can expect multiple matches for "db 05". E.g.:

PORTION	NETO	PROPORTION
db 05;db 34;db 05	34140	0,3;0,4;0,3

That is: 0,3 + 0,3.