software

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 MorningA 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.

quot;Davequot; wrote:

gt; I'm trying to count the unique values given several conditions, for example
gt; in the example below, count the number of unique Item_num's that were sold on
gt; Wed during the Morning, which would return 2:
gt;
gt; A B C
gt; 1: Item_Num Day Time
gt; 2: 1 Wed Morning
gt; 3: 1 Wed Morning
gt; 4: 2 Thur Evening
gt; 5: 2 Thur Evening
gt; 6: 3 Thur Morning
gt; 7: 3 Thur Morning
gt; 8: 4 Wed Evening
gt; 9: 4 Fri Evening
gt; 10: 5 Wed Morning
gt; 11: 5 Wed Morning
gt;
gt;
gt;
gt;

=SUM(--(FREQUENCY(IF((B2:B20=quot;Wedquot;)*(C2:C20=quot;Morningquot;),MA TCH(A2:A20,A2:A20,0
)),ROW(INDIRECT(quot;1:quot;amp;ROWS(A2:A20))))gt;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)

quot;Davequot; gt; wrote in message
...
gt; I'm trying to count the unique values given several conditions, for
example
gt; in the example below, count the number of unique Item_num's that were sold
on
gt; Wed during the Morning, which would return 2:
gt;
gt; A B C
gt; 1: Item_Num Day Time
gt; 2: 1 Wed Morning
gt; 3: 1 Wed Morning
gt; 4: 2 Thur Evening
gt; 5: 2 Thur Evening
gt; 6: 3 Thur Morning
gt; 7: 3 Thur Morning
gt; 8: 4 Wed Evening
gt; 9: 4 Fri Evening
gt; 10: 5 Wed Morning
gt; 11: 5 Wed Morning
gt;
gt;
gt;
gt;
Beautiful, thanks!quot;Bob Phillipsquot; wrote:

gt; =SUM(--(FREQUENCY(IF((B2:B20=quot;Wedquot;)*(C2:C20=quot;Morningquot;),MA TCH(A2:A20,A2:A20,0
gt; )),ROW(INDIRECT(quot;1:quot;amp;ROWS(A2:A20))))gt;0))
gt;
gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Davequot; gt; wrote in message
gt; ...
gt; gt; I'm trying to count the unique values given several conditions, for
gt; example
gt; gt; in the example below, count the number of unique Item_num's that were sold
gt; on
gt; gt; Wed during the Morning, which would return 2:
gt; gt;
gt; gt; A B C
gt; gt; 1: Item_Num Day Time
gt; gt; 2: 1 Wed Morning
gt; gt; 3: 1 Wed Morning
gt; gt; 4: 2 Thur Evening
gt; gt; 5: 2 Thur Evening
gt; gt; 6: 3 Thur Morning
gt; gt; 7: 3 Thur Morning
gt; gt; 8: 4 Wed Evening
gt; gt; 9: 4 Fri Evening
gt; gt; 10: 5 Wed Morning
gt; gt; 11: 5 Wed Morning
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;

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
gt; gt; gt; 1: Item_Num Country Time
gt; gt; gt; 2: 12816 Ireland 10/6/2008
gt; gt; gt; 3: 12816 Ireland 10/6/2008
gt; gt; gt; 4: 25222 Russia 10/8/2008
gt; gt; gt; 5: 21363 US 10/17/2008
gt; gt; gt; 6: 30741 Ireland 10/24/2008
gt; gt; gt; 7: 30741 Ireland 10/24/2008
gt; gt; gt; 8: 48622 US 10/25/2008
gt; gt; gt; 9: 44844 Ireland 10/28/2008
gt; gt; gt;10: 51008 Ireland 11/4/2008With the above data, I would expect to get (3).

Can you help?
quot;Davequot; wrote:

gt; Beautiful, thanks!
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =SUM(--(FREQUENCY(IF((B2:B20=quot;Wedquot;)*(C2:C20=quot;Morningquot;),MA TCH(A2:A20,A2:A20,0
gt; gt; )),ROW(INDIRECT(quot;1:quot;amp;ROWS(A2:A20))))gt;0))
gt; gt;
gt; gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; gt; just Enter.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Davequot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I'm trying to count the unique values given several conditions, for
gt; gt; example
gt; gt; gt; in the example below, count the number of unique Item_num's that were sold
gt; gt; on
gt; gt; gt; Wed during the Morning, which would return 2:
gt; gt; gt;
gt; gt; gt; A B C
gt; gt; gt; 1: Item_Num Day Time
gt; gt; gt; 2: 1 Wed Morning
gt; gt; gt; 3: 1 Wed Morning
gt; gt; gt; 4: 2 Thur Evening
gt; gt; gt; 5: 2 Thur Evening
gt; gt; gt; 6: 3 Thur Morning
gt; gt; gt; 7: 3 Thur Morning
gt; gt; gt; 8: 4 Wed Evening
gt; gt; gt; 9: 4 Fri Evening
gt; gt; gt; 10: 5 Wed Morning
gt; gt; gt; 11: 5 Wed Morning
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

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:C10gt;=F2)*(C2:C10lt;=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 MVPquot;Tom O.quot; lt;Tom gt; wrote in message
...
gt; I'm trying to accomplish something VERY similar. I'd like to count the
gt; number
gt; of unique values in Item_num that occur in Ireland between 10/1/2008 and
gt; 10/31/2008.
gt;
gt; A B C
gt;gt; gt; gt; 1: Item_Num Country Time
gt;gt; gt; gt; 2: 12816 Ireland 10/6/2008
gt;gt; gt; gt; 3: 12816 Ireland 10/6/2008
gt;gt; gt; gt; 4: 25222 Russia 10/8/2008
gt;gt; gt; gt; 5: 21363 US 10/17/2008
gt;gt; gt; gt; 6: 30741 Ireland 10/24/2008
gt;gt; gt; gt; 7: 30741 Ireland 10/24/2008
gt;gt; gt; gt; 8: 48622 US 10/25/2008
gt;gt; gt; gt; 9: 44844 Ireland 10/28/2008
gt;gt; gt; gt;10: 51008 Ireland 11/4/2008
gt;
gt;
gt; With the above data, I would expect to get (3).
gt;
gt; Can you help?
gt;
gt;
gt;
gt; quot;Davequot; wrote:
gt;
gt;gt; Beautiful, thanks!
gt;gt;
gt;gt;
gt;gt; quot;Bob Phillipsquot; wrote:
gt;gt;
gt;gt; gt; =SUM(--(FREQUENCY(IF((B2:B20=quot;Wedquot;)*(C2:C20=quot;Morningquot;),MA TCH(A2:A20,A2:A20,0
gt;gt; gt; )),ROW(INDIRECT(quot;1:quot;amp;ROWS(A2:A20))))gt;0))
gt;gt; gt;
gt;gt; gt; which is an array formula, it should be committed with
gt;gt; gt; Ctrl-Shift-Enter, not
gt;gt; gt; just Enter.
gt;gt; gt;
gt;gt; gt; --
gt;gt; gt; HTH
gt;gt; gt;
gt;gt; gt; Bob Phillips
gt;gt; gt;
gt;gt; gt; (remove nothere from email address if mailing direct)
gt;gt; gt;
gt;gt; gt; quot;Davequot; gt; wrote in message
gt;gt; gt; ...
gt;gt; gt; gt; I'm trying to count the unique values given several conditions, for
gt;gt; gt; example
gt;gt; gt; gt; in the example below, count the number of unique Item_num's that were
gt;gt; gt; gt; sold
gt;gt; gt; on
gt;gt; gt; gt; Wed during the Morning, which would return 2:
gt;gt; gt; gt;
gt;gt; gt; gt; A B C
gt;gt; gt; gt; 1: Item_Num Day Time
gt;gt; gt; gt; 2: 1 Wed Morning
gt;gt; gt; gt; 3: 1 Wed Morning
gt;gt; gt; gt; 4: 2 Thur Evening
gt;gt; gt; gt; 5: 2 Thur Evening
gt;gt; gt; gt; 6: 3 Thur Morning
gt;gt; gt; gt; 7: 3 Thur Morning
gt;gt; gt; gt; 8: 4 Wed Evening
gt;gt; gt; gt; 9: 4 Fri Evening
gt;gt; gt; gt; 10: 5 Wed Morning
gt;gt; gt; gt; 11: 5 Wed Morning
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;