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

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

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

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

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

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