Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
I'm trying to count the unique values given several conditions, for example
in the example below, count the number of unique Item_num's that were sold on Wed during the Morning, which would return 2: A B C 1: Item_Num Day Time 2: 1 Wed Morning 3: 1 Wed Morning 4: 2 Thur Evening 5: 2 Thur Evening 6: 3 Thur Morning 7: 3 Thur Morning 8: 4 Wed Evening 9: 4 Fri Evening 10: 5 Wed Morning 11: 5 Wed Morning |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
A pivot table may be an easy solution for you. Make Item_Num, Day, and Time
row fields. Make count of item # a data field. The resulting pivot table will show all the uunique Item_Num/Day/Time combinations and the # of times they exist in your list. You could also try selecting your entire range and then choose from the Excel menu path Data / Filter / Advanced Filter. And then choose Unique Reocords Only and choose Okay. "Dave" wrote: I'm trying to count the unique values given several conditions, for example in the example below, count the number of unique Item_num's that were sold on Wed during the Morning, which would return 2: A B C 1: Item_Num Day Time 2: 1 Wed Morning 3: 1 Wed Morning 4: 2 Thur Evening 5: 2 Thur Evening 6: 3 Thur Morning 7: 3 Thur Morning 8: 4 Wed Evening 9: 4 Fri Evening 10: 5 Wed Morning 11: 5 Wed Morning |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
=SUM(--(FREQUENCY(IF((B2:B20="Wed")*(C2:C20="Morning"),MA TCH(A2:A20,A2:A20,0
)),ROW(INDIRECT("1:"&ROWS(A2:A20))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave" wrote in message ... I'm trying to count the unique values given several conditions, for example in the example below, count the number of unique Item_num's that were sold on Wed during the Morning, which would return 2: A B C 1: Item_Num Day Time 2: 1 Wed Morning 3: 1 Wed Morning 4: 2 Thur Evening 5: 2 Thur Evening 6: 3 Thur Morning 7: 3 Thur Morning 8: 4 Wed Evening 9: 4 Fri Evening 10: 5 Wed Morning 11: 5 Wed Morning |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
Beautiful, thanks!
"Bob Phillips" wrote: =SUM(--(FREQUENCY(IF((B2:B20="Wed")*(C2:C20="Morning"),MA TCH(A2:A20,A2:A20,0 )),ROW(INDIRECT("1:"&ROWS(A2:A20))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave" wrote in message ... I'm trying to count the unique values given several conditions, for example in the example below, count the number of unique Item_num's that were sold on Wed during the Morning, which would return 2: A B C 1: Item_Num Day Time 2: 1 Wed Morning 3: 1 Wed Morning 4: 2 Thur Evening 5: 2 Thur Evening 6: 3 Thur Morning 7: 3 Thur Morning 8: 4 Wed Evening 9: 4 Fri Evening 10: 5 Wed Morning 11: 5 Wed Morning |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
I'm trying to accomplish something VERY similar. I'd like to count the number
of unique values in Item_num that occur in Ireland between 10/1/2008 and 10/31/2008. A B C 1: Item_Num Country Time 2: 12816 Ireland 10/6/2008 3: 12816 Ireland 10/6/2008 4: 25222 Russia 10/8/2008 5: 21363 US 10/17/2008 6: 30741 Ireland 10/24/2008 7: 30741 Ireland 10/24/2008 8: 48622 US 10/25/2008 9: 44844 Ireland 10/28/2008 10: 51008 Ireland 11/4/2008 With the above data, I would expect to get (3). Can you help? "Dave" wrote: Beautiful, thanks! "Bob Phillips" wrote: =SUM(--(FREQUENCY(IF((B2:B20="Wed")*(C2:C20="Morning"),MA TCH(A2:A20,A2:A20,0 )),ROW(INDIRECT("1:"&ROWS(A2:A20))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave" wrote in message ... I'm trying to count the unique values given several conditions, for example in the example below, count the number of unique Item_num's that were sold on Wed during the Morning, which would return 2: A B C 1: Item_Num Day Time 2: 1 Wed Morning 3: 1 Wed Morning 4: 2 Thur Evening 5: 2 Thur Evening 6: 3 Thur Morning 7: 3 Thur Morning 8: 4 Wed Evening 9: 4 Fri Evening 10: 5 Wed Morning 11: 5 Wed Morning |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Values
Try this array formula**. Assumes Item_Num are numeric numbers.
E2 = Ireland F2 = 10/1/2008 G2 = 10/31/2008 =COUNT(1/FREQUENCY(IF((B2:B10=E2)*(C2:C10=F2)*(C2:C10<=G2) ,A2:A10),A2:A10)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Tom O." <Tom wrote in message ... I'm trying to accomplish something VERY similar. I'd like to count the number of unique values in Item_num that occur in Ireland between 10/1/2008 and 10/31/2008. A B C 1: Item_Num Country Time 2: 12816 Ireland 10/6/2008 3: 12816 Ireland 10/6/2008 4: 25222 Russia 10/8/2008 5: 21363 US 10/17/2008 6: 30741 Ireland 10/24/2008 7: 30741 Ireland 10/24/2008 8: 48622 US 10/25/2008 9: 44844 Ireland 10/28/2008 10: 51008 Ireland 11/4/2008 With the above data, I would expect to get (3). Can you help? "Dave" wrote: Beautiful, thanks! "Bob Phillips" wrote: =SUM(--(FREQUENCY(IF((B2:B20="Wed")*(C2:C20="Morning"),MA TCH(A2:A20,A2:A20,0 )),ROW(INDIRECT("1:"&ROWS(A2:A20))))0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Dave" wrote in message ... I'm trying to count the unique values given several conditions, for example in the example below, count the number of unique Item_num's that were sold on Wed during the Morning, which would return 2: A B C 1: Item_Num Day Time 2: 1 Wed Morning 3: 1 Wed Morning 4: 2 Thur Evening 5: 2 Thur Evening 6: 3 Thur Morning 7: 3 Thur Morning 8: 4 Wed Evening 9: 4 Fri Evening 10: 5 Wed Morning 11: 5 Wed Morning |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
how to count unique values in excel based on multiple criteria | Excel Worksheet Functions | |||
count and display unique values | Excel Worksheet Functions | |||
count number of values between plus signs in addition calc | Excel Discussion (Misc queries) | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions |