Count Unique function in sheet with data filter applied

[TedH]

I have a weekly sheet generated by a customer I need some help with....

Sheet can be 1500 lines deep; I need to count unique values in one column while using a filter on two or three other columns.
I am familiar with the "advanced" method that copies all unique values to another column, but this is a really clunky way to do it and I know there has to be a better way.
Plus I need to count the uniques when the filters are applied and compare the results....

And to make things _more_ complicated, this is a shared file that two or three people may be working with at a time, so the "advanced" button on the filter is grayed out.

I attached a snippet of this weeks file.

Thanks!
Ted

[FDibbins]

try using =subtotal(2,your-range)

[benishiryo]

hi Ted, maybe:
=SUMPRODUCT(1/COUNTIF($E$5:$E$11,$E$5:$E$11)*(SUBTOTAL(3,OFFSET($E$4,ROW($E$5:$E$11)-ROW($E$5)+1,))))

[icestationzbra]

try ARRAY-entered:

Please Login or Register  to view this content.

[Fotis1991]

Maybe one way

=SUM(IF(FREQUENCY(IF((SUBTOTAL(3,OFFSET($E$4,ROW($E$5:$E$11)-MIN(ROW($E$5:$E$11))+1,0))),MATCH($E$5:$E$11,$E$5:$E$11,0)),ROW($E$5:$E$11)-MIN(ROW($E$5:$E$11))+1),1))

Edit: @ ICE Too fast for me!

[Tony Valko]

Try this array formula**:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(E5,ROW(E5:E11)-ROW(E5),0,1)),MATCH(E5:E11,E5:E11,0)),ROW(E5:E11)-ROW(E5)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[TedH]

All:

Thanks for the suggestions....this is what I found when I tried them:

Tony Valko & FOTIS: results right on >>THANKS!<<

benishiryo: when I sort by FA (columnL) I don't get a whole number

fdibbons: subtotal doesn't filter out repetitive entries....

Now if I can figure out the differences b/t how Tony and Fotis get the same answer I'll feel better; might take some serious brainpower and time.

Greatly appreciated.
Ted

[Tony Valko]

You're welcome. Thanks for the feedback!