Advanced dynamic array, filter, unique count

[Razzy]

Hi!

I have received help here on the forum before to create these formulas, but now I am a bit stuck.

I need that the result also takes into account unique types of "type".

The question is simple: "How do I add an extra filter so that it only counts for the unique type?"

It is also possible to use the column named: "Nr." to determine unique types. Then we avoid the problem of "text".

Hope to hear from you!

Mr. Excel sample.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
45	Nr.	Type	Count	Dimension		Length	Prefix		Helper column - test			Result		I need it to also filer by:			New result
46	1203	Ø400	2	0,4	0,4	2,75	R		0,2816			4		Ø400			2
47	1205	Ø500	2	0,5	0,5	3,3	R		0,825					Ø500
48	1213	>900cm2	1	0,3	0,4	3,3	F		0,20625					>900cm2
49	1213	>900cm2	1	0,3	0,5	3,3	F		0,38148					300X600
50	1213	>900cm2	1	0,4	0,4	3,3	F		0,33792
51	1213	>900cm2	1	0,4	0,6	3,3	F		0,89232
52	1213	>900cm2	2	0,4	0,4	2,75	F		0,2816
53	1207	300X600	1	0,3	0,6	2,8	O1		0,567
54	1207	300X600	2	0,3	0,6	2,2	O1		0,4455
55	1207	300X600	4	0,3	0,6	2,4	O1		0,486
56	1213	>900cm2	1	0,3	0,5	2,8	F		0,32368
57	1213	>900cm2	1	0,4	0,4	2,8	F		0,28672
58	1213	>900cm2	1	0,4	0,6	2,8	F		0,75712
59									0
60									0
61									0
62									0
63									0
64									0
65									0
66									0
67									0
Ark1

Cell Formulas
Range	Formula
K46:K246	K46	=(F46:F246^2+G46:G246^2)^2*H46:H246
N46	N46	=IFERROR(SUM(FILTER(SUMIFS(E46:E246,K46#,UNIQUE(FILTER((F46:F246^2+G46:G246^2)^2*H46:H246,E46:E246))),SUMIFS(E46:E246,K46#,UNIQUE(FILTER((F46:F246^2+G46:G246^2)^2*H46:H246,E46:E246)))>2),0)-2*COUNT(FILTER(SUMIFS(E46:E246,K46#,UNIQUE(FILTER((F46:F246^2+G46:G246^2)^2*H46:H246,E46:E246))),(SUMIFS(E46:E246,K46#,UNIQUE(FILTER((F46:F246^2+G46:G246^2)^2*H46:H246,E46:E246)))>2))),0)
P46:P49	P46	=UNIQUE(D46:D58)
Dynamic array formulas.

 

[alansidman]

Is this what you are looking for? Since you only provided results for one item, I was not sure

Book15
	A	B
1	Type	Data
2	Ø400	2
3	Ø500	2
4	>900cm2	9
5	300X600	7
Sheet2

	​
	​
	​
	​

 

[Razzy]

Hi! I need this formula to also filter / consider the "Nr." column, so the new result becomes 2.

I "just" need to ad an extra filer in the formula below:

Thx!

Mr. Excel sample.xlsx
	N
46	4
Ark1

Cell Formulas
Range	Formula
N46	N46	=IFERROR(SUM(FILTER(SUMIFS(E46:E246,K46#,UNIQUE(FILTER((F46:F246^2+G46:G246^2)^2*H46:H246,E46:E246))),SUMIFS(E46:E246,K46#,UNIQUE(FILTER((F46:F246^2+G46:G246^2)^2*H46:H246,E46:E246)))>2),0)-2*COUNT(FILTER(SUMIFS(E46:E246,K46#,UNIQUE(FILTER((F46:F246^2+G46:G246^2)^2*H46:H246,E46:E246))),(SUMIFS(E46:E246,K46#,UNIQUE(FILTER((F46:F246^2+G46:G246^2)^2*H46:H246,E46:E246)))>2))),0)

 

[Fluff]

It would be easier, if you told us what you are trying to do, rather than rely on us to try & deconstruct your formula.