| Book1 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | 1 | 15 | 100 | 14.44 | Sumproduct/Sum (average) if D="o" | ||||||
| 2 | 2 | 30 | 200 | ||||||||
| 3 | 3 | 9 | 100 | o | 21 | 1,600 | Sum of C1:C20 where D="o" | 1,800 | |||
| 4 | 4 | 20 | 400 | o | 20.5 | ||||||
| 5 | 5 | 10 | 100 | o | 19.3 | ||||||
| 6 | 6 | 10 | 200 | o | 17.6 | ||||||
| 7 | 7 | 25 | 100 | o | 18.3 | ||||||
| 8 | 8 | 10 | 300 | o | 16.6 | ||||||
| 9 | 9 | 30 | 100 | o | 17.4 | ||||||
| 10 | 10 | 9 | 300 | o | 16.1 | ||||||
| 11 | 11 | 40 | 100 | ||||||||
| 12 | 12 | 8.5 | 100 | ||||||||
| 13 | 13 | 10.5 | 100 | ||||||||
| 14 | 14 | 10 | 200 | ||||||||
| 15 | 15 | 35 | 100 | ||||||||
| 16 | 16 | 9.5 | 200 | ||||||||
| 17 | 17 | 9 | 500 | ||||||||
| 18 | 18 | 25 | 100 | ||||||||
| 19 | 19 | 10.5 | 100 | ||||||||
| 20 | 20 | 10 | 200 | ||||||||
| 21 | |||||||||||
Sheet2 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F1 | F1 | =IF(SUM(E1:E20)<=0,"",ROUND(SUMPRODUCT((INDEX($B1:$B20,MATCH("*",$D1:$D20,0)-0,0):INDEX($B1:$B20,MATCH("*",$D1:$D20,-1)-0,0)),(INDEX($C1:$C20,MATCH("*",$D1:$D20,0)-0,0):INDEX($C1:$C20,MATCH("*",$D1:$D20,-1)-0,0)))/SUM(INDEX($C1:$C20,MATCH("*",$D1:$D20,0)-0,0):INDEX($C1:$C20,MATCH("*",$D1:$D20,-1)-0,0)),2)) |
| F3 | F3 | =IFERROR(IF(SUM(E1:E20)<=0,"",SUM(INDEX($C1:$C20,MATCH("*",$D1:$D20,0)-0,0):(INDEX($C1:$C20,MATCH("*",$D1:$D20,-1)-0,0)))),"") |
| E1:E20 | E1 | =IFERROR(IF($D1<>"o","",ROUND(SUMPRODUCT(B$1:B1,C$1:C1)/SUM(C$1:C1),1)),"") |
the formula in F1 and F3 works fine with the "o" in continuous cells, but what is i want the calculation for it if the "o" is in following,
| Book1 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | 1 | 15 | 100 | 15.13 | Sumproduct/Sum (average) if D="o" | 1 | ||||||
| 2 | 2 | 30 | 200 | o | 25 | |||||||
| 3 | 3 | 9 | 100 | 3,100 | Sum of C1:C20 where D="o" | 1,800 | ||||||
| 4 | 4 | 20 | 400 | |||||||||
| 5 | 5 | 10 | 100 | o | 19.3 | |||||||
| 6 | 6 | 10 | 200 | o | 17.6 | |||||||
| 7 | 7 | 25 | 100 | |||||||||
| 8 | 8 | 10 | 300 | |||||||||
| 9 | 9 | 30 | 100 | |||||||||
| 10 | 10 | 9 | 300 | o | 16.1 | |||||||
| 11 | 11 | 40 | 100 | o | 17.3 | |||||||
| 12 | 12 | 8.5 | 100 | |||||||||
| 13 | 13 | 10.5 | 100 | o | 16.6 | |||||||
| 14 | 14 | 10 | 200 | |||||||||
| 15 | 15 | 35 | 100 | o | 16.8 | |||||||
| 16 | 16 | 9.5 | 200 | o | 16.3 | |||||||
| 17 | 17 | 9 | 500 | o | 15.1 | |||||||
| 18 | 18 | 25 | 100 | |||||||||
| 19 | 19 | 10.5 | 100 | |||||||||
| 20 | 20 | 10 | 200 | |||||||||
| 21 | ||||||||||||
Sheet1 | ||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F1 | F1 | =IF(SUM(E1:E20)<=0,"",ROUND(SUMPRODUCT((INDEX($B1:$B20,MATCH("*",$D1:$D20,0)-0,0):INDEX($B1:$B20,MATCH("*",$D1:$D20,-1)-0,0)),(INDEX($C1:$C20,MATCH("*",$D1:$D20,0)-0,0):INDEX($C1:$C20,MATCH("*",$D1:$D20,-1)-0,0)))/SUM(INDEX($C1:$C20,MATCH("*",$D1:$D20,0)-0,0):INDEX($C1:$C20,MATCH("*",$D1:$D20,-1)-0,0)),2)) |
| F3 | F3 | =IFERROR(IF(SUM(E1:E20)<=0,"",SUM(INDEX($C1:$C20,MATCH("*",$D1:$D20,0)-0,0):(INDEX($C1:$C20,MATCH("*",$D1:$D20,-1)-0,0)))),"") |
| E1:E20 | E1 | =IFERROR(IF($D1<>"o","",ROUND(SUMPRODUCT(B$1:B1,C$1:C1)/SUM(C$1:C1),1)),"") |
Last edited:
