| Sample.xlsx | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | Quantity | A (m) | B (m) | Hight (m) | Type | Test1 | Test2 | test | antall | sortert | sum | filtrert | vektet | |||||
| 2 | 1 | 0,3 | 0,2 | 3,34 | F | 0,017 | 0,056 | 1 | 0,017 | 1 | 0,640 | 1 | 1 | 1 | ||||
| 3 | 15 | 0,3 | 0,1 | 2,19 | O1 | 0,010 | 0,022 | 2 | 0,000 | 0 | 0,203 | 5 | 5 | 1 | ||||
| 4 | 4 | 0,3 | 0,5 | 3,34 | F | 0,116 | 0,386 | 3 | 0,116 | 4 | 0,116 | 6 | 6 | 1 | ||||
| 5 | 5 | 0,3 | 0,6 | 1,5 | F | 0,203 | 0,304 | 4 | 0,203 | 5 | 0,116 | 6 | 0 | 0 | ||||
| 6 | 1 | 0,3 | 0,5 | 1 | F | 0,116 | 0,116 | 5 | 0,116 | 1 | 0,116 | 6 | 0 | 0 | ||||
| 7 | 1 | 0,4 | 0,8 | 1,5 | F | 0,640 | 0,960 | 6 | 0,640 | 1 | 0,017 | 1 | 1 | 1 | ||||
| 8 | 32 | 0,3 | 0,6 | 1 | R | 0,203 | 0,203 | 7 | 0,000 | 0 | 0,000 | 0 | 0 | 0 | ||||
| 9 | 1 | 0,3 | 0,5 | 1 | F | 0,116 | 0,116 | 8 | 0,116 | 1 | 0,000 | 0 | 0 | 0 | ||||
| 10 | Result | 4 | ||||||||||||||||
| 11 | ||||||||||||||||||
| 12 | Bucket | 10 | test1 | 4 | ||||||||||||||
| 13 | ||||||||||||||||||
| 14 | test2 | 60 | ||||||||||||||||
| 15 | test2 | antall | sortert | sum | filtrert | vektet | ||||||||||||
| 16 | 1 | 3,357 | 1,000 | 3,4556 | 4 | 4 | 2 | |||||||||||
| 17 | 2 | 2,200 | 15,000 | 3,3569 | 1 | 1 | 0 | |||||||||||
| 18 | 3 | 3,456 | 4,000 | 2,2 | 15 | 15 | 13 | |||||||||||
| 19 | 4 | 1,703 | 5,000 | 2,14 | 1 | 1 | 0 | |||||||||||
| 20 | 5 | 1,116 | 1,000 | 1,7025 | 5 | 5 | 3 | |||||||||||
| 21 | 6 | 2,140 | 1,000 | 1,2025 | 32 | 32 | 30 | |||||||||||
| 22 | 7 | 1,203 | 32,000 | 1,1156 | 2 | 2 | 0 | |||||||||||
| 23 | 8 | 1,116 | 1,000 | 1,1156 | 2 | 0 | 0 | |||||||||||
| 24 | Result | 48 | ||||||||||||||||
Ark1 | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F2:F9 | F2 | =(B2:B9^2+C2:C9^2)^2 |
| G2:G9 | G2 | =(B2:B9^2+C2:C9^2)^2*D2:D9 |
| K2:K9 | K2 | =IF(AND((B2^2+C2^2)^2>0,E2="F"),(B2^2+C2^2)^2,0) |
| L2:L9 | L2 | =IF(K2<>0,A2,0) |
| M2:M9 | M2 | =IFERROR(LARGE($K$2:$K$9,J2),"") |
| N2:N9 | N2 | =SUMIF($K$2:$K$9,M2,$L$2:$L$9) |
| O2:O9 | O2 | =IF(M2<>M1,SUMIF($K$2:$K$9,M2,$L$2:$L$9),0) |
| P2:P9 | P2 | =IFERROR(ROUNDUP(O2/$B$12,0),0) |
| P10,P24 | P10 | =SUM(P2:P9) |
| H12 | H12 | =SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"F",F2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2,(E2:E9="F"))))/B12,0)) |
| H14 | H14 | =SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"*",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,ISNUMBER(SEARCH("*",G2:G9))))),0)) |
| K16:K23 | K16 | =IF(((B2^2+C2^2)^2+D2)>0,((B2^2+C2^2)^2+D2),0) |
| L16:L23 | L16 | =IF(K16<>0,A2,0) |
| M16:M23 | M16 | =IFERROR(LARGE($K$16:$K$23,J16),"") |
| N16:N23 | N16 | =SUMIF($K$16:$K$23,M16,$L$16:$L$23) |
| O16:O23 | O16 | =IF(M16<>M15,SUMIF($K$16:$K$23,M16,$L$16:$L$23),0) |
| P16:P23 | P16 | =IF(O16>2,O16-2,0) |
| Dynamic array formulas. | ||
How do I replica "test2" in cell H14? Se manual result gives 48 as correct answer.
I need to get Excel to think total.
If it finds a match, it need to find all the same matches, and take minus 2. Only if there are more > 2 pcs.
Was that understandable?
Thx to Mr. Fluff for guidance on previous post. ? 
