Unique Cells in Column

Alan Patridge:
"I know lying is wrong, but if the elephant man came in now in a blouse with some make up on, and said "How do I look?" Would you say, bearing in mind he's depressed and has respiratory problems, would you say "Go and take that blusher off you mis-shapened elephant tranny!" No, you'd say "You look nice... John"

Click to expand...

mikechambers said:

Something I can't understand. If Excel can quickly count the number of unique cells in a column of data when you apply a filter, then why isn't there a function to return that number? Seems like a given, right? Is there a way to "tap into" the filter somehow with VBA or a custom function?

I just have a list of dates in a column, and I need to figure out how many different days are included in it so I can get an average.

Click to expand...

T. Valko said:

This array formula** will count the unique dates in the filtered (or unfiltered) range A3:A20.

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A3,ROW(A3:A20)-ROW(A3),)),A3:A20),A3:A20),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.

Click to expand...

Matty said:

Assuming your dates are in A2:A11, try:

Code:

=SUM(IF(FREQUENCY(IF(A2:A11<>"",MATCH(A2:A11,A2:A11,0)),ROW(A2:A11)-ROW(A2)+1),1))

Which requires array entering, i.e. with CTRL+SHIFT+ENTER.

Matty

Click to expand...

mikechambers said:

I got the correct answer even without CSE. Thanks!

Click to expand...