Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Subtotal of "unique" values 2
- Thread starter CandyS
- Start date
-
1
- #2
- Thread starter
- #3
Thanks, tav.
I need the Autofilter to enable users to "drill down" into the data, so, unfortunately, it's integral to the tool.
For example, if I filter on, say, "Philadelphia" in the "city" column and 100 addresses are returned in the "address" column, I would like to subtotal the count of unique addresses in the filtered list instead of the count of the entire range.
Candy
I need the Autofilter to enable users to "drill down" into the data, so, unfortunately, it's integral to the tool.
For example, if I filter on, say, "Philadelphia" in the "city" column and 100 addresses are returned in the "address" column, I would like to subtotal the count of unique addresses in the filtered list instead of the count of the entire range.
Candy
-
1
- #4
SkipVought
Programmer
Candy,
"SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered."
Give MS Excel Help a STAR!
Skip,
Skip@TheOfficeExperts.com
"SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered."
Give MS Excel Help a STAR!
Skip,
Skip@TheOfficeExperts.com
- Thread starter
- #5
Thanks Skip--and you reminded of the "advanced filter" option in Autofilter (but that can't help me here...).
But is there a way to subtotal the unique values in the visible range of a filtered list?
My intuition says "no", but then again I'm not a Wizard (not even an apprentice witch yet!).
I can't give MS Excel Help a star, but I'll gladly give you one (and you, too, tav--thanks!).
Thanks,
Candy
But is there a way to subtotal the unique values in the visible range of a filtered list?
My intuition says "no", but then again I'm not a Wizard (not even an apprentice witch yet!).
I can't give MS Excel Help a star, but I'll gladly give you one (and you, too, tav--thanks!).
Thanks,
Candy
SkipVought
Programmer
Candy,
Did you read the quote?
=SUBTOTAL(9,YourNumberList)
only SUMS THE VISIBLE CELLS in YourNumberList!
Skip,
Skip@TheOfficeExperts.com
Did you read the quote?
=SUBTOTAL(9,YourNumberList)
only SUMS THE VISIBLE CELLS in YourNumberList!
Skip,
Skip@TheOfficeExperts.com
CandyS,
After you filter your selections, highlight the values you want to sum, and it will sum at the bottom in the grey area just above your taskbar. If not, right click on the grey area and choose sum.
It's crude but works for quick reference.
tav
After you filter your selections, highlight the values you want to sum, and it will sum at the bottom in the grey area just above your taskbar. If not, right click on the grey area and choose sum.
It's crude but works for quick reference.
tav
- Status
Similar threads
