Unique values NOT including hidden rows!

[Leozack]

Hi guys, been playing with excel a lot recently and I've finally come unstuck. I have a formula that happily tells me how many unique entries there are in a column of dates C11:C995 in dd/mm/yyyy format. The formula is

=SUM(IF(FREQUENCY(C11:C995,C11:C995)>0,1))

However, I have sorting and filtering on this list, and when only say half the results are being shown, this formula is still calculating based on ALL the results, including those hidden. I was overjoyed to learn of the SUBTOTAL function and the 10X numbers to ignore hidden rows, but I failed to find a way to impliment it into the frequency range anywhere.
Any help? :/

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

To put my total stuff in. But I guess I could just have total stuff up the top above the table where it is currently. Though I would still need to extend the range it reads from as the table extends. Or just say like 99999. Unless I could say like "all of column F in table tablename1" or something

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[SkipVought]

I would never recommend putting totals at the bottom of a table of indeterinate length, or ANY table, for that matter. Why make the poor user search for the "bottom line"? That's a throwback to paper, pencil and adding machine.

As a metter of design principle, I never put ANY data BELOW.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Leozack]

heh my totals are currently above the "table" which is a frozen pane for scrolling - no worries about putting it underneath lol. The column formulas need adding for each new row once I reach the predone amount, and the total cells would need their range extended for new rows beyond the current max too.
If I made it a table I may not need to change the column formulas if it automatically adds them for all new table rows, but would still need to extend the range in the total cells as the table got bigger (or anytime it reached the max range I set in the total cells, currently 995 for no particular reason)

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[SkipVought]

and the total cells would need their range extended for new rows beyond the current max too.

Huh?
[tt]
=aggregation_formula_of_your_choice(A4:A65526)
[/tt]
can't go any further!


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Leozack]

Yes I could do something massive like that you're right ;P I'll contemplate tables next time, though I'd still need my unique-date column smewhere, and I wouldn't want it visible, so I'd have to make a 0 width column or something like it is now, inside the table

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[GSCaupling]

Hi Leozack,

I wonder if a PivotTable would solve the problem.

Using your data:
[tt]
Date Term
1/4/09 1
1/4/09 1
2/4/09 1
2/4/09 1
3/4/09 2
3/4/09 2
4/22/09 2
4/23/09 2
4/23/09 2[/tt]


I get the following PT:
[tt]
[red]Count[/red] of Term Column Labels
Row Labels 1 2 Grand Total
1/4/09 2 2
2/4/09 2 2
3/4/09 2 2
4/22/09 1 1
4/23/09 2 2
Grand Total 4 5 9
[/tt]
Date Cnt: [red]5[/red]


I added the "Date Cnt" below the PivotTable. The PT has a filter function so you can view just the Term 1s or Term 2s. Everything else adjusts automatically.

Good luck!
GS



[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[Leozack]

tbh there's actually a load of columns ,I just chose to show thsoe 2 as a means to haeing a reason for not all the dates to be showing. I briefly looked at PTs but don't think they're relevant here. This is effectively a log chart for everyone to add their initials and the hours they spent and on what task and what day etc, so you could be filtering for any of that, and it needs t be continually added too. The totals up the top are for total hours, total hours travelled, entries shown (including after filtering) and days shown (the troublesome one)

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

OK I've made it a table, I've made lookup lists for some of the columns so they can use pulldowns (since I couldn't find a shortcut for "right click, select pick from dropdown list" which was handy for choosing from already entered data in that column, so I hadda make the lookup lists myself on a 2nd worksheet), and I've made a hidden column to show the dates only if not hidden and use this column for my "total days shown" cell. Previously I was using this formula for that cell
=SUM(IF(FREQUENCY(C11:C995,C11:C995)>0,1))-1
since empty rows had '0' displayed using this formula
=if(sum(A1)=subtotal(9,A1),A1,"")
but I changed that to be
=if(sum(A1)=subtotal(9,A1),IF(A1<>"",A1,""),"")
so now it doesn't show '0', it shows nothing if A1 is blank (no date, empty row). So now my total counting cell is
=SUM(IF(FREQUENCY(C11:C995,C11:C995)>0,1))
with no -1 since there isn't an extra unique value ebing counted of '0'

However! 1 annoyance remains ... if I opena new file and make a quick table, if I start typing on the line below the table it automatically expands the table down. But if I try this on my actual table I'm working with, it doesn't automatically extend teh table down to niclude the new row you're typing in! Why? It makes no sense. Using the handle in the corner of the table or pressing tab on that lower right cell adds a new row as expected, but why isn't it adding the new row when you just start entering data in the row under the table, as it does on dummy tables I make from scratch?

_________________________________
Leozack

MakeUniverse($infinity,1,42);