Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting the number of unique values in a filtered list
Hi!
I've been battling with this for a while now, I hope you guys can help! :) I'm using Office 2007 Enterprise on Win XP Pro SP3. I have a sheet of data comprising store names, product quantities, product prices, etc. The store names are in the first column (text values, obviously), with product names, prices, quantities, etc in subsequent columns. There are at present about 50 columns and 2500 rows of data, with no blank rows. This data is filtered. What I need to do is to count the number of unique values in a given column (for example the number of stores that stock a particular product, with the list having been filtered by that particular product name). I do not need to know the frequency or the names of the stores - just how many there are in the filtered list. The answer given to this particular question [https://www.microsoft.com/office/com...en-us&m=1&p=1] is the closest I have found so far to giving me what I need, but this does not adjust for when rows are filtered / hidden. I don't particularly want to use a pivot-table; that would be rather cumbersome to use easily. I need to use this result in other calculations. Actually, I'm a bit surprised that a function of this nature has yet to be built-in to Office 2007. I hope this doesn't stump you guys like it has me! :) -- Philip |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting the number of unique values in a filtered list
Since you are using a filterd list I assume it is AutoFilter. Is this
correct? Have you tried using SUBTOTAL function. Look it up in Help for all of the various mathematical calcs it can return and automatically updates each time the filter is changed. -- Regards, OssieMac "Bannor" wrote: Hi! I've been battling with this for a while now, I hope you guys can help! :) I'm using Office 2007 Enterprise on Win XP Pro SP3. I have a sheet of data comprising store names, product quantities, product prices, etc. The store names are in the first column (text values, obviously), with product names, prices, quantities, etc in subsequent columns. There are at present about 50 columns and 2500 rows of data, with no blank rows. This data is filtered. What I need to do is to count the number of unique values in a given column (for example the number of stores that stock a particular product, with the list having been filtered by that particular product name). I do not need to know the frequency or the names of the stores - just how many there are in the filtered list. The answer given to this particular question [https://www.microsoft.com/office/com...en-us&m=1&p=1] is the closest I have found so far to giving me what I need, but this does not adjust for when rows are filtered / hidden. I don't particularly want to use a pivot-table; that would be rather cumbersome to use easily. I need to use this result in other calculations. Actually, I'm a bit surprised that a function of this nature has yet to be built-in to Office 2007. I hope this doesn't stump you guys like it has me! :) -- Philip |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting the number of unique values in a filtered list
have a look at advanced filter, too.
-- Gary Keramidas Excel 2003 "Bannor" wrote in message ... Hi! I've been battling with this for a while now, I hope you guys can help! :) I'm using Office 2007 Enterprise on Win XP Pro SP3. I have a sheet of data comprising store names, product quantities, product prices, etc. The store names are in the first column (text values, obviously), with product names, prices, quantities, etc in subsequent columns. There are at present about 50 columns and 2500 rows of data, with no blank rows. This data is filtered. What I need to do is to count the number of unique values in a given column (for example the number of stores that stock a particular product, with the list having been filtered by that particular product name). I do not need to know the frequency or the names of the stores - just how many there are in the filtered list. The answer given to this particular question [https://www.microsoft.com/office/com...en-us&m=1&p=1] is the closest I have found so far to giving me what I need, but this does not adjust for when rows are filtered / hidden. I don't particularly want to use a pivot-table; that would be rather cumbersome to use easily. I need to use this result in other calculations. Actually, I'm a bit surprised that a function of this nature has yet to be built-in to Office 2007. I hope this doesn't stump you guys like it has me! :) -- Philip |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting the number of unique values in a filtered list
This will count the unique items in column A of a filtered list.
Assume the full unfiltered range is A2:A20. Array entered** : =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),)),MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Bannor" wrote in message ... Hi! I've been battling with this for a while now, I hope you guys can help! :) I'm using Office 2007 Enterprise on Win XP Pro SP3. I have a sheet of data comprising store names, product quantities, product prices, etc. The store names are in the first column (text values, obviously), with product names, prices, quantities, etc in subsequent columns. There are at present about 50 columns and 2500 rows of data, with no blank rows. This data is filtered. What I need to do is to count the number of unique values in a given column (for example the number of stores that stock a particular product, with the list having been filtered by that particular product name). I do not need to know the frequency or the names of the stores - just how many there are in the filtered list. The answer given to this particular question [https://www.microsoft.com/office/com...en-us&m=1&p=1] is the closest I have found so far to giving me what I need, but this does not adjust for when rows are filtered / hidden. I don't particularly want to use a pivot-table; that would be rather cumbersome to use easily. I need to use this result in other calculations. Actually, I'm a bit surprised that a function of this nature has yet to be built-in to Office 2007. I hope this doesn't stump you guys like it has me! :) -- Philip |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting the number of unique values in a filtered list
T.Valko - You're the man!!! Works perfectly! Thankyou very much! :)
OssieMac & Gary Keramidas - thanks for trying, but I don't believe either the SUBTOTAL function nor an advanced filter will give me exactly what I was looking for - I had already looked at both options extensively before my post. -- Philip "T. Valko" wrote: This will count the unique items in column A of a filtered list. Assume the full unfiltered range is A2:A20. Array entered** : =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),)),MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting the number of unique values in a filtered list
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Bannor" wrote in message ... T.Valko - You're the man!!! Works perfectly! Thankyou very much! :) OssieMac & Gary Keramidas - thanks for trying, but I don't believe either the SUBTOTAL function nor an advanced filter will give me exactly what I was looking for - I had already looked at both options extensively before my post. -- Philip "T. Valko" wrote: This will count the unique items in column A of a filtered list. Assume the full unfiltered range is A2:A20. Array entered** : =COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),)),MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of unique values within a range | Excel Discussion (Misc queries) | |||
How do I lookup the number of unique values from a list? | Excel Discussion (Misc queries) | |||
Counting unique values in a list generated with the OFFSET functio | Excel Worksheet Functions | |||
Counting unique text entries in a filtered list... | Excel Worksheet Functions | |||
counting unique numbers in filtered data | Excel Worksheet Functions |