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);

 

[SkipVought]

Hi,

Use the SUBTOTAL function with the 109 function number argument.

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

 

[Leozack]

Or possibly I could have an invisible column that was storing just the unique values and then count those using subtotal ignoring hidden, but I dunno how I'd store the unique values automatically on the fly through sorting/filtering etc?

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[SkipVought]

Whatever floats your boat!

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

 

[Leozack]

I use the subtotal formula for totalling the filtered numbers eg

=SUBTOTAL(103,B11:B995)

but the problem is I can't find a way to get subtotal into the formular for calculating unique values from my range which is

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

I've tried things like

=SUBTOTAL(109,IF(FREQUENCY(C11:C995,C11:C955)>0,1))

but I figured the thing that needs 'subtotaling' as it were (the range that needs to have hidden values removed) is the FREQUENCY(C11:C995,C11:C955) part, and I can't get subtotal into there can I? Because I don't want to totaly or group what's there before I do the "find the unique" frequency part?

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[SkipVought]

Please post a data example (a portion of your table) and what you expect the results to be for that example.

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

 

[SkipVought]

and what is the LOGIC for the hidden rows? Is there a value(s) that can be tested to determine that the row is hidden?

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

 

[Leozack]

Sure. My data column currently looks like this

Date (dd/mm/yyyy)          Term
01/04/2009               1
01/04/2009               1
02/04/2009               1
02/04/2009               1
03/04/2009               2
03/04/2009               2
22/04/2009               2
23/04/2009               2
23/04/2009               2

If I have a cell with this formula for that column
=SUM(IF(FREQUENCY(C11:C995,C11:C995)>0,1))
then it would say "4" since there are 4 unique values (Days) on the list.

But if I filter the list so that eg only term 1 rows are showing, then the unique field cell will still be using the full range and siaplaying "4" when only the first 4 rows and thus only 2 unique fields are present and it should only show "2".

Does that explain it?

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[SkipVought]

and the HIDDEN row logic?

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

 

[Leozack]

Done by filtering by the end user, eg "lets look at only data for X" or "lets only look at data from these dates" using standard excel sorting/filtering

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[SkipVought]

So it's based on selection(s) in a FILTER and not just selecting & hiding rows.

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

 

[Leozack]

Correct it's done by runtime selective filtering
Hence I'd like the "TOTAL DAYS" to not show the number of unique days in the data (often more than 1 row per day) but to show the number of unique days in the SHOWN data. Subtotal worked wonders for totalling hours and other column data ignoring hidden rows, but just can't do it for my total days cell Wish excel had a UNIQUE function that counted unique entries from ranges, and then that could be a function inside SUBTOTAL like 11/111

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

So - does anyone have a way to select unique values from a column range IGNORING filtering-hidden rows? :/

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

=SUM(IF(FREQUENCY(C11:C996,C11:C956)>0,1))
is my formula that counts unique values in whole range
but to try and make it hidden only after googling stuff on other sites I tried
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C11,ROW(C11:C995)-ROW(C11),0)),C11:C995),C11:C995)>0,1))
but that doesn't work either Same result, total unique in all range including hidden.
May try this but I don't see how it could automatically work without requiring a button or something :/

http://www.eggheadcafe.com/community/aspnet/66/10069792/try-this.aspx

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Leozack]

I've now found more examples from

http://spreadsheetpage.com/index.php/site/eee/issue_no_20_july_8_2001/

but I think it's more VBA requiring activation and such, I just wanted a formula in a cell that constantly stays updated? I'm not an excel pro as you can tell

Define a column range in your table (excluding header) as Rge.

Define unRge as:
=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,"")

Then, the array formula to return the # of unique occurrences in a filtered 
column is:

=SUM(N(IF(ISNA(MATCH("",unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)
=MATCH("",unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1))

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[Gavona]

Or possibly I could have an invisible column that was storing just the unique values and then count those using subtotal ignoring hidden, but I dunno how I'd store the unique values automatically on the fly through sorting/filtering etc?

How about a variation on that?
To test if the row is visible you could use in your helper column: =sum(A1)=subtotal(9,A1) or similar. For numeric values this returns True if the cell is visible, False if hidden through filtering.
Combine that with an IF so that your helper column only contains values for Date (I assume in column A)when the row is visible.
=if(sum(A1)=subtotal(9,A1),A1,"")
Now use the approach you already have working to count the unique values.

Gavin

 

[Leozack]

Thanks! I made another column and used that as the formula and made is 0.1 thick. The only problem being I had to guess how many row to use (all upto 995 rows for now, can add more rows later) which gave it a nice tiny scrollbar having pasted the formula into all those cells

But the real problem was I got a "0" entry in every cell that was on a blank row yet to be filled in. So I had to add a -1 onto my
=SUM(IF(FREQUENCY(J11:J995,J11:J995)>0,1))
row to make the summary cell
=SUM(IF(FREQUENCY(C11:C995,C11:C995)>0,1))-1
Seems to be working so far, but I'll haev to make sure I always add the formula to rows over 995 and chance expand the ranges in all my formulas!

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[SkipVought]

I had to guess how many row to use

Use Data > List -Create Table...

Forget all those formulas in empty rows! As you enter new data in your TABLE, the formula(s) in adjacent column (and within the table) will propogate to the new row.

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

 

[Leozack]

I tried some tables beforehand and as much as it was cool and coloured itself and so on, it did things like total rows that I need but need to have customised and I can't say "add custom empty row under table data". Also dunno how I'd have the required invisible column since if it's in a table then the table gobbles it up into its formatting and colours etc May have a paly though, though for now it's ok at 995 and when we hit that I can just column copy/paste the row formulas and asjsut the 995 number on the total cells up the top?

_________________________________
Leozack

MakeUniverse($infinity,1,42);

 

[SkipVought]

I can't say "add custom empty row under table data

WHY a "custom empty row under table data?"

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