Count number of unique names in a filtered list (non array)

Hi

I have a list of names in column B2:B1394, along with other data in adjacent columns.

After filtering the data from the adjacent columns, how do I then return the number of unique names in column B (ideally using a non-array formula).

I have previously used:
=SUMPRODUCT((B2:B1394<>"")/COUNTIF(B2:B1394,B2:B1394))
But this does not work once the list is filtered.

I am using Excel 2003 if that makes a difference.

Many thanks in advance.

Thanks sktneer but unfortunately this still only gives me the unique total of everything in cells B2:B1394, even after the filters are applied.

Any other ideas???

Sample document attached, basically if I filter "Job Description" by "Bus Driver" I want the total of column E (red) to say 3 (as there are 2 F's).

The current formula is right when un-filtered, but does not change when the filter is applied.

Thanks

Hi XOR LX

The array formula works great (thanks) but was hoping for a non-array solution it possible?

Yes, I probably should have just explained the whole situation in the first place!!!

Basically I have a large volume of employee data on one sheet which includes name, workplace, grade, salary, etc... I am trying to create a summary on a different sheet that automatically populates the number of people at each grade by workplace.

I have been successfully using the following formula on the summary sheet to lookup all the employees on a particular grade (cell ref: C$3), working at a particular site (cell ref B5), and return the total of the filtered data in column P:

=+SUMPRODUCT(('Data'!$N$2:$N$1394='Summary'!$B5)*('Data'!$L$2:$L$1394='Summary'!C$3)*('Data'!$P$2:$P$1394))

Next to this I wanted to show the actual number of people (headcount) bearing in mind that some employees will be on the filtered list more than once. I therefore tried the array formula you suggested as follows:

=+SUMPRODUCT(('Data'!$N$2:$N$1394='Summary'!$B5)*('Data'!$L$2:$L$1394='Summary'!C$3)*(SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET('Data'!$B$2,ROW('Data'!$B$2:$B$1394)-MIN(ROW('Data'!$B$2:$B$1394)),,,)),MATCH('Data'!$B$2:$B$1394,'Data'!$B$2:$B$1394,0)),ROW('Data'!$B$2:$B$1394)-MIN(ROW('Data'!$B$2:$B$1394))+1)>0))))

Unfortunatley this does not remove any duplicates and basically just tells me how many lines (including duplicates) have been filtered. I thought this may be because I am using an array formula within a normal formula, given that the array formula works on its own when used on the data sheet itself. Hence I was asking for a non-array version, but I'm sure there's a better way anyhow!

Hope this kind of makes sense and thanks for all your help so far.

I attach a second example which should show what I mean...

The array formula is in cell G19 (red figure). When you filter "Grade" to "1" and "Workplace" to "Wales" G19 changes to "2" - this is correct as "E" appears twice in the "name" column.

So my question is, how do I get the same result to show in the "summary" sheet cell "C5" as this currently returns "3" counting all the lines including the duplicate "E".

Thanks

That seems to have done the trick, thanks for all your help.