# How to make Excel think total, and not line by line

#### Razzy

##### Board Regular
Sample.xlsx
ABCDEFGHIJKLMNOP
1QuantityA (m)B (m)Hight (m)TypeTest1Test2testantallsortertsumfiltrertvektet
210,30,23,34F0,0170,05610,01710,640111
3150,30,12,19O10,0100,02220,00000,203551
440,30,53,34F0,1160,38630,11640,116661
550,30,61,5F0,2030,30440,20350,116600
610,30,51F0,1160,11650,11610,116600
710,40,81,5F0,6400,96060,64010,017111
8320,30,61R0,2030,20370,00000,000000
910,30,51F0,1160,11680,11610,000000
10Result4
11
12Bucket10test14
13
14test260
15test2antallsortertsumfiltrertvektet
1613,3571,0003,4556442
1722,20015,0003,3569110
1833,4564,0002,2151513
1941,7035,0002,14110
2051,1161,0001,7025553
2162,1401,0001,2025323230
2271,20332,0001,1156220
2381,1161,0001,1156200
24Result48
Ark1
Cell Formulas
RangeFormula
F2:F9F2=(B2:B9^2+C2:C9^2)^2
G2:G9G2=(B2:B9^2+C2:C9^2)^2*D2:D9
K2:K9K2=IF(AND((B2^2+C2^2)^2>0,E2="F"),(B2^2+C2^2)^2,0)
L2:L9L2=IF(K2<>0,A2,0)
M2:M9M2=IFERROR(LARGE(\$K\$2:\$K\$9,J2),"")
N2:N9N2=SUMIF(\$K\$2:\$K\$9,M2,\$L\$2:\$L\$9)
O2:O9O2=IF(M2<>M1,SUMIF(\$K\$2:\$K\$9,M2,\$L\$2:\$L\$9),0)
P2:P9P2=IFERROR(ROUNDUP(O2/\$B\$12,0),0)
P10,P24P10=SUM(P2:P9)
H12H12=SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"F",F2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2,(E2:E9="F"))))/B12,0))
H14H14=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:K23K16=IF(((B2^2+C2^2)^2+D2)>0,((B2^2+C2^2)^2+D2),0)
L16:L23L16=IF(K16<>0,A2,0)
M16:M23M16=IFERROR(LARGE(\$K\$16:\$K\$23,J16),"")
N16:N23N16=SUMIF(\$K\$16:\$K\$23,M16,\$L\$16:\$L\$23)
O16:O23O16=IF(M16<>M15,SUMIF(\$K\$16:\$K\$23,M16,\$L\$16:\$L\$23),0)
P16:P23P16=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?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Razzy

##### Board Regular
Sample.xlsx
ABCDEFGHIJKLMNOP
1QuantityA (m)B (m)Hight (m)TypeTest1Test2testantallsortertsumfiltrertvektet
210,30,23,34F0,0170,05610,01710,017881
310,30,23,34F0,0170,05620,01710,017800
410,30,23,34F0,0170,05630,01710,017800
510,30,23,34F0,0170,05640,01710,017800
610,30,23,34F0,0170,05650,01710,017800
710,30,23,34F0,0170,05660,01710,017800
810,30,23,34F0,0170,05670,01710,017800
910,30,23,34F0,0170,05680,01710,017800
10Result1
11
12Bucket10test11
13
14test28
15test2antallsortertsumfiltrertvektet
1613,3571,0003,3569886
1723,3571,0003,3569800
1833,3571,0003,3569800
1943,3571,0003,3569800
2053,3571,0003,3569800
2163,3571,0003,3569800
2273,3571,0003,3569800
2383,3571,0003,3569800
24Result6
Ark1
Cell Formulas
RangeFormula
F2:F9F2=(B2:B9^2+C2:C9^2)^2
G2:G9G2=(B2:B9^2+C2:C9^2)^2*D2:D9
K2:K9K2=IF(AND((B2^2+C2^2)^2>0,E2="F"),(B2^2+C2^2)^2,0)
L2:L9L2=IF(K2<>0,A2,0)
M2:M9M2=IFERROR(LARGE(\$K\$2:\$K\$9,J2),"")
N2:N9N2=SUMIF(\$K\$2:\$K\$9,M2,\$L\$2:\$L\$9)
O2:O9O2=IF(M2<>M1,SUMIF(\$K\$2:\$K\$9,M2,\$L\$2:\$L\$9),0)
P2:P9P2=IFERROR(ROUNDUP(O2/\$B\$12,0),0)
P10,P24P10=SUM(P2:P9)
H12H12=SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"F",F2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2,(E2:E9="F"))))/B12,0))
H14H14=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:K23K16=IF(((B2^2+C2^2)^2+D2)>0,((B2^2+C2^2)^2+D2),0)
L16:L23L16=IF(K16<>0,A2,0)
M16:M23M16=IFERROR(LARGE(\$K\$16:\$K\$23,J16),"")
N16:N23N16=SUMIF(\$K\$16:\$K\$23,M16,\$L\$16:\$L\$23)
O16:O23O16=IF(M16<>M15,SUMIF(\$K\$16:\$K\$23,M16,\$L\$16:\$L\$23),0)
P16:P23P16=IF(O16>2,O16-2,0)
Dynamic array formulas.

Some times I have many of the same type, on seperate lines.

#### Razzy

##### Board Regular
I could do this, but still not correct answer (48): 52 is wrong.

Sample.xlsx
ABCDEFGHIJKLMNOP
1QuantityA (m)B (m)Hight (m)TypeTest1Test2testantallsortertsumfiltrertvektet
210,30,23,34F0,0170,05610,01710,640111
3150,30,12,19O10,0100,02220,00000,203551
440,30,53,34F0,1160,38630,11640,116661
550,30,61,5F0,2030,30440,20350,116600
610,30,51F0,1160,11650,11610,116600
710,40,81,5F0,6400,96060,64010,017111
8320,30,61R0,2030,20370,00000,000000
910,30,51F0,1160,11680,11610,000000
10Result4
11
12Bucket10test14
13
14test260
15test2-8test2antallsortertsumfiltrertvektet
16test25213,3571,0003,4556442
1722,20015,0003,3569110
1833,4564,0002,2151513
1941,7035,0002,14110
2051,1161,0001,7025553
2162,1401,0001,2025323230
2271,20332,0001,1156220
2381,1161,0001,1156200
24Result48
Ark1
Cell Formulas
RangeFormula
F2:F9F2=(B2:B9^2+C2:C9^2)^2
G2:G9G2=(B2:B9^2+C2:C9^2)^2*D2:D9
K2:K9K2=IF(AND((B2^2+C2^2)^2>0,E2="F"),(B2^2+C2^2)^2,0)
L2:L9L2=IF(K2<>0,A2,0)
M2:M9M2=IFERROR(LARGE(\$K\$2:\$K\$9,J2),"")
N2:N9N2=SUMIF(\$K\$2:\$K\$9,M2,\$L\$2:\$L\$9)
O2:O9O2=IF(M2<>M1,SUMIF(\$K\$2:\$K\$9,M2,\$L\$2:\$L\$9),0)
P2:P9P2=IFERROR(ROUNDUP(O2/\$B\$12,0),0)
P10,P24P10=SUM(P2:P9)
H12H12=SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"F",F2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2,(E2:E9="F"))))/B12,0))
H14H14=SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"*",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,ISNUMBER(SEARCH("*",G2:G9))))),0))
H15H15=COUNT(UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,A2:A9>2)))*-2
H16H16=SUM(ROUNDUP(SUMIFS(A2:A9,E2:E9,"*",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,ISNUMBER(SEARCH("*",G2:G9))))),0))+COUNT(UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,A2:A9>2)))*-2
K16:K23K16=IF(((B2^2+C2^2)^2+D2)>0,((B2^2+C2^2)^2+D2),0)
L16:L23L16=IF(K16<>0,A2,0)
M16:M23M16=IFERROR(LARGE(\$K\$16:\$K\$23,J16),"")
N16:N23N16=SUMIF(\$K\$16:\$K\$23,M16,\$L\$16:\$L\$23)
O16:O23O16=IF(M16<>M15,SUMIF(\$K\$16:\$K\$23,M16,\$L\$16:\$L\$23),0)
P16:P23P16=IF(O16>2,O16-2,0)
Dynamic array formulas.

#### Razzy

##### Board Regular
H15: =COUNT(UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,A2:A9>2)))*-2

The include part gets wrong: "A2:A9>2", because that refers to the single rows.

I need a formula that looks at the unique values in a total.

Above I need the answer to be 48.

#### Razzy

##### Board Regular
i'll figure it out. Thx

#### Razzy

##### Board Regular
Sample.xlsx
I
3448
Ark1
Cell Formulas
RangeFormula
I34I34=SUM(FILTER(SUMIFS(A2:A9,E2:E9,"*",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,A2:A9))),SUMIFS(A2:A9,E2:E9,"*",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,A2:A9)))>1),0)-2*COUNT(FILTER(SUMIFS(A2:A9,E2:E9,"*",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,A2:A9))),(SUMIFS(A2:A9,E2:E9,"*",G2#,UNIQUE(FILTER((B2:B9^2+C2:C9^2)^2*D2:D9,A2:A9)))>1)))

My solution Thx to Mr. Fluff for guidance on previous post.

Replies
1
Views
41
Replies
3
Views
84
Replies
13
Views
130
Replies
2
Views
95
Replies
1
Views
38