Count Unique Values

[Dave]

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

 

[William Horton]

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.

 

[Bob Phillips]

=SUM(--(FREQUENCY(IF((B2:B20="Wed")*(C2:C20="Morning"),MATCH(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]

Beautiful, thanks!

=SUM(--(FREQUENCY(IF((B2:B20="Wed")*(C2:C20="Morning"),MATCH(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)

 

[Tom O.]

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

With the above data, I would expect to get (3).

Can you help?

 

[T. Valko]

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)