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

Razzy

Board Regular
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

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
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
i'll figure it out. Thx
 

Razzy

Board Regular
Joined
Jul 24, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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. 👏 (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,942
Messages
5,545,114
Members
410,660
Latest member
marciabkin
Top