google sheets - Googlesheets SUMPRODUCT with position - Stack Overflow

admin管理员组

文章数量:1387334

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

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.

• google-sheets
• google-sheets-formula
• spreadsheet
• sumproduct

Share Improve this question Follow edited Mar 18 at 8:11 ouroboros1 14.8k77 gold badges4848 silver badges5858 bronze badges asked Mar 18 at 6:27 Fernando Arns DergFernando Arns Derg 311 silver badge22 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 3

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.

THANK YOU!!!! I didn't even knew about =MAP and LAMBDA, this worked wonderfully!!!

Sample

=SUM(
  MAP(D4:D; L4:L; W4:W; 
    LAMBDA(portion; neto; proportion; 
      IFERROR(
        IF(proportion = ""; 1; INDEX(SPLIT(proportion; ";"); MATCH(Z3; SPLIT(portion; ";"); 0))) * neto;
        0
      )
    )
  )
)

This badboy got me the sum of every "L4:L" (NETO) where "D4:D" is "mz 22" (Z3) in the right proportion based on its position, "W4:W" (first positon in the first line), plus every other "mz 22" in the "D4:D".

=IFERROR(
  INDEX(
    SPLIT(IF(W4 = ""; "1"; W4); ";"); 
    MATCH($Z$3; SPLIT(D4; ";"); 0)
  ) * L4; 
  ""
)

I also added this formula for the individual line to get the NETO of the desired match "mz 22" (Z3)

Again, thanks a lot!!!

Edit: The "db 05;db 34;db 05" was a typo, the last "db 05" was suposed to be "db 07".

本文标签： google sheetsGooglesheets SUMPRODUCT with positionStack Overflow