UNIQUE(FILTER(INDIRECT formula

[craigexcel]

Using Office 365.

It appears I must have a syntax error in my formula, but I haven't been able to resolve yet.
While this version works, where I specifically reference the range of data to use (i.e. Row 1185) ....
=SORT(UNIQUE(FILTER(combined!B6:C1185,NOT(ISNUMBER(SEARCH("Total",combined!B6:B1185))))),{1,2},1,FALSE)

this dynamic version (since the source is a pivot table in an adjacent sheet) does NOT work, and returns #VALUE! .....
=SORT(UNIQUE(FILTER(INDIRECT("combined!B6:C"&COUNT(combined!D6:D5000)),NOT(ISNUMBER(SEARCH("Total",combined!B6:B5000))))),{1,2},1,FALSE)

I also tried changing INDIRECT("combined!B6:C"&COUNT(combined!D6:D5000 ..... to ..... INDIRECT("combined!B6:C"&COUNTA(combined!B6:B5000 thinking maybe the Column needed to be in sync, but that didn't work either.
{Column B in the "combined" sheet pivot table has text; Column D has values.}

I chose Row 5000 as that should always be a sufficiently large range. I assume the issue is with the use of the INDIRECT function, as the SORT and UNIQUE seem reasonably straightforward.
Can someone provide an extra set of eyes to pinpoint the issue?? This has to be something simple.

 

[KRice]

The issue you're encountering is due to the two arrays being different sizes. INDIRECT is relying on a COUNT to define the lower end of the range...and the range begins on row 6. Yet the NOT(ISNUMBER array is an array consisting of 4995 (5000-6+1) elements. The two arrays need to be the same length. What are you attempting to do?...construct a dynamic range for the FILTER function?

 

[KRice]

You might try an approach like this to form dynamic ranges without using a volatile function like INDIRECT. Here, two dynamic ranges are constructed for the FILTER function, and the -5 accounts for beginning the range at row 6. Other information on the sheet might affect the approach to form these arrays, so test carefully.

Book1
	B	C	D	E	F	G
5					Unique
6	a	D	6		a	D
7	b	C	7		b	C
8	c	B	8		c	B
9	Total	A	9		c	Z
10	a	D	6		d	A
11	b	C	7		d	G
12	c	Z	8
13	d	A	9
14	Total	D	6
15	b	C	7
16	c	B	8
17	d	G	9
combined

Cell Formulas
Range	Formula
F6:G11	F6	=SORT(UNIQUE(FILTER($B$6:INDEX($C$6:$C$5000,MAX((D:D<>"")*(ROW(D:D)))-5),NOT(ISNUMBER(SEARCH("Total",$B$6:INDEX($B$6:$B$5000,MAX((D:D<>"")*(ROW(D:D)))-5)))))),{1,2},1,FALSE)
Dynamic array formulas.

 

[KRice]

I had another look and you might be able to avoid forming dynamic ranges altogether if you are able to eliminate the blank entries in columns B:C, as done here in the example labeled "Alternative"...where (B6:B5000<>"") will keep only those rows in the range of interest.

Book1
	B	C	D	E	F	G	H	I	J
5					Unique			Alternative
6	a	D	6		a	D		a	D
7	b	C	7		b	C		b	C
8	c	B	8		c	B		c	B
9	Total	A	9		c	Z		c	Z
10	a	D	6		d	A		d	A
11	b	C	7		d	G		d	G
12	c	Z	8
13	d	A	9
14	Total	D	6
15	b	C	7
16	c	B	8
17	d	G	9
combined

Cell Formulas
Range	Formula
F6:G11	F6	=SORT(UNIQUE(FILTER($B$6:INDEX($C$6:$C$5000,MAX((D:D<>"")*(ROW(D:D)))-5),NOT(ISNUMBER(SEARCH("Total",$B$6:INDEX($B$6:$B$5000,MAX((D:D<>"")*(ROW(D:D)))-5)))))),{1,2},1,FALSE)
I6:J11	I6	=SORT(UNIQUE(FILTER(B6:C5000,(B6:B5000<>"")*NOT(ISNUMBER(SEARCH("Total",B6:B5000))))),{1,2},1,FALSE)
Dynamic array formulas.

 

[craigexcel]

Thx KRice for your extra set of eyes & your replies. I appreciate it. I opted to use the formula shared in the previous post, for cell [I6].
I see what I overlooked, with the ranges I was referencing.

 

[KRice]

Glad to help…thanks for the feedback!