|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 26th, 2006, 11:56 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I set up a formula to count only unique items in a column?
For Example: If I have a column with 8 items but two are duplicates. I want
the formula to return a value of only the unique items (6) ABC DEF GHI JKL MNO PQR ABC DEF 
|
|
#2
March 26th, 2006, 11:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set up a formula to count only unique items in a colu
Thanks Jennifer
it worked great Lyle "jennifer" wrote: I take no credit; Not my creation: =SUMPRODUCT((A1:A30"")/COUNTIF(A1:A30,A1:A30&"")) or =SUMPRODUCT(--(A1:A30""),1/COUNTIF(A1:A30,A1:A30&"")) "LYLERR" wrote: For Example: If I have a column with 8 items but two are duplicates. I want the formula to return a value of only the unique items (6) ABC DEF GHI JKL MNO PQR ABC DEF
|
|
#3
September 24th, 2009, 10:06 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set up a formula to count only unique items in a colu
Do you know how to display the unique value in a cell (ABC, DEF, GHI, JKL,
MNO or PQR) instead of count the unique items? For example in a separate column, rows 1 - 6 will display ABC, DEF, GHI, JKL, MNO and PQR but in rows 7 and 8, ABC and DEF not be duplicated...will be blank. "jennifer" wrote: I take no credit; Not my creation: =SUMPRODUCT((A1:A30"")/COUNTIF(A1:A30,A1:A30&"")) or =SUMPRODUCT(--(A1:A30""),1/COUNTIF(A1:A30,A1:A30&"")) "LYLERR" wrote: For Example: If I have a column with 8 items but two are duplicates. I want the formula to return a value of only the unique items (6) ABC DEF GHI JKL MNO PQR ABC DEF
|
|
#4
September 25th, 2009, 12:53 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I set up a formula to count only unique items in a colu
Hi,
You may use Advanced Filters. Select the range including the header row. Now go to Data Filter Advanced Filter and select Copy to another location. in the list range, specify the range of cells (incl. the header row) and leave the criteria range blank. In the copy to box, select any blank cell and check the box for unique records only. Hope this helps./ -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "JDub" wrote in message ... Do you know how to display the unique value in a cell (ABC, DEF, GHI, JKL, MNO or PQR) instead of count the unique items? For example in a separate column, rows 1 - 6 will display ABC, DEF, GHI, JKL, MNO and PQR but in rows 7 and 8, ABC and DEF not be duplicated...will be blank. "jennifer" wrote: I take no credit; Not my creation: =SUMPRODUCT((A1:A30"")/COUNTIF(A1:A30,A1:A30&"")) or =SUMPRODUCT(--(A1:A30""),1/COUNTIF(A1:A30,A1:A30&"")) "LYLERR" wrote: For Example: If I have a column with 8 items but two are duplicates. I want the formula to return a value of only the unique items (6) ABC DEF GHI JKL MNO PQR ABC DEF
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Positioning Numeric Values Resulting from 6 Column Array Formula | Sam via OfficeKB.com | Worksheet Functions | 2 | January 5th, 2006 02:03 AM |
| Formula Problem - interrupted by #VALUE! in other cells!? | Ted | Worksheet Functions | 17 | November 25th, 2005 05:18 PM |
| Return Count for LAST NonBlank Cell in each Row | Sam via OfficeKB.com | Worksheet Functions | 12 | April 17th, 2005 10:36 PM |
| Count Unique Items | Regan | Worksheet Functions | 3 | April 28th, 2004 02:57 PM |
Linear Mode
