Combine 'unique' function with 'filter' function

[hungledink]

I'm trying to work out how to combine 2 separate functions into one cell and was wondering if anyone could assist.

I have a sample sheet of data, which has data in columns A to C, and rows 1 to 21. (image below)

I have used the filter function in cell E2 to filter out a specific value from column C, this produces a spill range, starting in cell E2 and then however many rows down it finds the criteria. I then use the unique function in cell E23, to count the unique values returned from the initial filter.

I can get the 2 formulas to work separately, but I have to manual enter the range in the unique filter which isnt ideal since the number of rows is dynamic.

I wondered if I could combine the 2 formulas into 1 cell?

 

[Eric W]

Try:

E23:

=ROWS(UNIQUE(INDEX(E3#,0,1)))

 

[hungledink]

Try:

E23:

=ROWS(UNIQUE(INDEX(E3#,0,1)))

Once I'd replaced the E3# with the filter formula worked nicely. Thanks for the help