Dynamic Consolidate Error

[rcb007]

In the Cell O9 and Q9, it shows a #Value! I am having a hard time trying to figure out if it's something in the pervious column that is causing the error or not.

Thank you.

170240000-Storm Calculations.xlsm
	K	L	M	N	O	P	Q
1	name - type	area	c		Consolidated	area	c
2	585 - Roof	907.51	0.30		298	6467.09	0.90
3	585 - Pavement	119.74	0.30		500	9576.60	0.85
4	298 - Pavement	6198.46	0.90		501	27735.09	0.60
5	632 - Pavement	6055.31	0.90		537	4600.78	0.85
6	632 - Grass	1196.09	0.90		585	1027.25	0.30
7	537 - Pavement	2993.44	0.85		601	21123.28	0.85
8	632 - Roof	1058.00	0.85		632	8309.40	0.88
9	601 - Pavement	18195.57	0.85		#VALUE!	0.00	#DIV/0!
10	601 - Grass	1899.09	0.85
11	500 - Grass	8191.78	0.85
12	501 - Pavement	18457.33	0.30
13	501 - Grass	9277.75	0.90
14	601 - Roof	1028.62	0.85
15	500 - Pavement	1384.82	0.85
16	537 - Grass	664.33	0.85
17	537 - Roof	943.00	0.85
18	298 - Grass	268.63	0.90
Imported Parcels

Cell Formulas
Range	Formula
K2:K18	K2	=UNIQUE(FILTER(I2:I1002,I2:I1002<>""))
L2:L18	L2	=SUMIFS(C:C,I:I,K2#)
M2:M18	M2	=AVERAGEIFS(G2:G1002,K2:K1002,K2#&"*")
O2:O9	O2	=SORT(UNIQUE(LEFT(K2:K1002,FIND(" ",K2:K1002)-1)))
P2:P9	P2	=SUMIFS(L2:L1002,K2:K1002,O2#&"*")
Q2:Q9	Q2	=AVERAGEIFS(M2:M1002,K2:K1002,O2#&"*")
Dynamic array formulas.

 

[JamesCanale]

I would guess that some of the values in K2:K1002 don't have a 'space' in it, throwing a number of #VALUE! errors. Then the UNIQUE turns it into one. Put it in an error handling bit,

 

[Fluff]

For O2 use

=SORT(UNIQUE(LEFT(K2#,FIND(" ",K2#)-1)))

 

[bobsan42]

in K2:K1002 you have a blank cell or a cell that does not contain a space

 

[rcb007]

I did think about if there was a space within the value which would cause the error.

Fluff. again, you make it simple. I did not think of that as well.

You fixed it! Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.