Posted to microsoft.public.excel.worksheet.functions

Ashish Mathur[_2_] Ashish Mathur[_2_] is offline

external usenet poster

Posts: 1,766

Ignore Hidden Rows and Count of distinct values

Hi,

Try this. Assumed that data is in L3:21

=COUNT(1/FREQUENCY(IF(SUBTOTAL(103,OFFSET(L3,ROW(L3:L21)-ROW(L3),)),MATCH(L3:L21,L3:L21,0)),ROW(L3:L21)-ROW(L3)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Anuma (GGK Tech)" wrote in message
...
Hi,

I need to display the Count of distinct values and also ignore hidden rows
in the count.
Please help me to fix this issue.

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Count Distinct Values based on criteria	FinChase	Excel Worksheet Functions	5	July 8th 09 07:25 PM
Functions to ignore hidden rows	hmm	Excel Worksheet Functions	2	June 5th 07 05:26 PM
How do I ignore values while copying from hidden rows?	Mayank	Excel Worksheet Functions	1	July 19th 05 02:48 PM
Count Distinct Values?	bill_morgan	Excel Worksheet Functions	7	April 27th 05 02:12 AM
Ignore Hidden Rows in Sum Function?	Jugglertwo	Excel Discussion (Misc queries)	3	February 10th 05 01:00 AM

All times are GMT +1. The time now is 06:44 PM.

About Us

"It's about Microsoft Excel"

Menu

About Us