Unique days in a list sorted by Auto Sort filter

[z1habanero]

I have a list of dates and times that are sorted using Excels "AutoSort Filter".

The spreadsheet is 6000 rows total, with only around 450 filled at this time. So rows beyond the ones currently filled in are blank.

After I use the AutoSort filter I may have only 100 rows showing. These are the rows I would like to get the number of unique dates from, not the rows not showing.

I would also like it if it would give the number of unique days when no AutoSort filter is being used.

All dates are and will always be entered chronologically.
Example dates and times below. Date and time are entered in one cell.

<TABLE style="WIDTH: 99pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=132 border=0><COLGROUP><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><TBODY><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0 0.5pt; BORDER-LEFT: #f0f0f0 0.5pt; WIDTH: 99pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" width=132 height=17>12/3/07 12:39 PM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/3/07 2:48 PM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/3/07 3:39 PM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/5/07 4:35 PM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/7/07 5:07 PM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/8/07 10:50 AM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/8/07 11:55 AM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/14/07 12:05 PM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/15/07 9:26 AM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/18/07 10:31 AM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/18/07 3:00 PM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/19/07 2:34 PM</TD></TR><TR style="HEIGHT: 13.15pt; mso-height-source: userset" height=17><TD class=xl66 style="BORDER-RIGHT: #f0f0f0 0.5pt; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0 0.5pt; BORDER-BOTTOM: #f0f0f0 0.5pt; HEIGHT: 13.15pt; BACKGROUND-COLOR: transparent" height=17>12/20/07 10:07 AM</TD></TR></TBODY></TABLE>

The formula I am using now is......
=SUM(IF(LEN(A4:A6000),1/(COUNTIF(A4:A6000,"<"&INT(A4:A6000)+1)-COUNTIF(A4:A6000,"<"&INT(A4:A6000)))))

This formula has a problem.
1. The number of unique dates does not change after I use the AutoSort filter.

I tried inserting the =SUBTOTAL(9,A4:A6000) function into the formula above but I could not get it to work.

I have put lots of hours into this and I really need help.

Z1habanero

 

[venkat1926]

I think you have to copy the auto-filterd data in another locations and get unique data by using again "advance filter"

if neessary you can create macro right from the start (autofilter stage).

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A4:A6000,ROW(A4:A6000)-ROW(A4),0,1)),INT(A4:A6000)),IF(SUBTOTAL(3,OFFSET(A4:A6000,ROW(A4:A6000)-ROW(A4),0,1)),INT(A4:A6000))),1))

Hope this helps!

 

[z1habanero]

Dominic, you are amazing!!!! It works fantastic! I am so happy.
Thank you,
Thank you,
Thank you.

z1habanero